"André Hartmann" <andrehartmann@hotmail.com> wrote in message news:<4138944c$1@olaf.komtel.net>...
> Hi,
>
> I wonder if the following is possible. Given an Oracle 9i client on
> Solaris and a ksh (KornShell) there is it possible to write a ksh script and
> trigger for example SQL*Plus to pull out a BLOB from a remote 9i Oracle
> Server and store the contents of the LOB in a file on the client ?
>
> I am aware that you can write .sql files and batch them under SQL*Plus and
> I also know the @ command line option of SQL*Plus. My problem is more on the
> LOB side. I know LOBs are fetched in a special way (LOB locator) so I wonder
> how to pipe the LOB data to a local file.
>
> In particular I do not want to write and compile a C program that uses OCI
> (it would be trivial using that) and also I do not want to use Perl/DBI.
>
> So, Is it possible with SQL*Plus or any other tool that comes with an
> Oracle client ? Is SQLLoader of any help ?
>
> Thanks,
>
> André
>

Have you looked at Oracle's manual on LOB (BLOB or CLOB). You can use
PL/SQL blocks inside
sql*plus to do what you want. In PL/SQL Oracle has an interface to
write output
to a file using UTIL_FILE package or so. If I am not mistaken
PL/SQL's buffer can only
be 32K bytes, so if your LOB is longer than 32K, you run into
problems.

I did exactly what you are looking for using java. If you are familiar
with
java stored procedures, you can write a stored procedure to do this
task
or you can use Java programming outside Oracle (that is what I did)
and write some JDBC code
to do the same. Nice thing about Java is that it does not have PL/SQL
limitation
of 32K. In the LOB manual, Oracle describes how to use PL/SQL, JAVA,
OCI, PRO*C, etc.

Prem