Re: Oracle9i: How to pull LOB data via SQL*Plus ?

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<4138944c$1_at_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 Received on Fri Sep 03 2004 - 19:50:19 CDT