SEO

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é

On Fri, 3 Sep 2004 17:56:59 +0200, "André Hartmann"
<andrehartmann@hotmail.com> wrote:

>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é
>
>

The dbms_lob package has functions for this.


--
Sybrand Bakker, Senior Oracle DBA

"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

> The dbms_lob package has functions for this.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Which function(s) are you referring to ? I only found functions that work
with BFILE and as far as I know BFILE refers to an external LOB (file) on
the Oracle Server. I want to store into client side files though.

AH

> 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.

Using java for client language is a good idea. At least it is platform
independent. If I dont find a way to do it with SQL*PLUS directly, I will
certainly fall back to writing a little jave/JDBY thingy.

AH

On Sat, 4 Sep 2004 15:16:28 +0200, "André Hartmann"
<andrehartmann@hotmail.com> wrote:

>
>> 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.
>
> Using java for client language is a good idea. At least it is platform
>independent. If I dont find a way to do it with SQL*PLUS directly, I will
>certainly fall back to writing a little jave/JDBY thingy.
>
>AH
>
>

PL/SQL is pretty platform independent too. You''ll only need to read
the doco on the dbms_lob package.


--
Sybrand Bakker, Senior Oracle DBA

"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 ?

SQL*Plus currently can fetch CLOBs but not BLOBs.

Can you tell us a little more on how you want to use the data? If a
future version of SQL*Plus were going to support BLOBs, what
commands/interface would be useful to you? Do you want the end result
to be a binary file? Since SQL*Plus output is traditionally character
based would a column of, say, uuencoded or base64 data be better than
hex encoding?

-- CJ

cjbj wrote:

> "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 ?
>
>
> SQL*Plus currently can fetch CLOBs but not BLOBs.
>
> Can you tell us a little more on how you want to use the data? If a
> future version of SQL*Plus were going to support BLOBs, what
> commands/interface would be useful to you? Do you want the end result
> to be a binary file? Since SQL*Plus output is traditionally character
> based would a column of, say, uuencoded or base64 data be better than
> hex encoding?
>
> -- CJ

and if what is stuffed into that BLOB file is greater than a terabye in
size what would you then propose to do with it?

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

> Can you tell us a little more on how you want to use the data? If a
> future version of SQL*Plus were going to support BLOBs, what
> commands/interface would be useful to you? Do you want the end result
> to be a binary file? Since SQL*Plus output is traditionally character
> based would a column of, say, uuencoded or base64 data be better than
> hex encoding?

In the cutomer's database we have binary content stored in LOBs, the
binary content being for example .gif files, CCD (Catia Cadam Draft) files
(that is CAD models), MS Office documents or fractions of those, movie files
and so on.

The customer requests a shell script (nothing compiled like C/C++) that is
able to issue a SQL statement that would select a particulat LOB, for
example "SELECT CCDContents FROM CADLibrary WHERE Id=4711" (where
CCDContents is a BLOB column and Id is the primary key) and store the result
(the stream of bytes that makes the LOB) in a local file on the client
computer.

With a little convincing we might get away with a Java/JDBC application
if it is not possible otherwise. But of course I have to evaluate first
whether it is possible otherwise, that's why I am doing.

We do not expect to be the LOB size around a tera byte. The argest
files/LOBs we have seen in our environment so far are 500MB and its not
likely to exceed 1GB per LOB in the future. TB is just not a use case (for
us) so I dont think about that.

AH

"André Hartmann" <andrehartmann@hotmail.com> wrote in message
news:413eb8fa$1@olaf.komtel.net...
| > Can you tell us a little more on how you want to use the data? If a
| > future version of SQL*Plus were going to support BLOBs, what
| > commands/interface would be useful to you? Do you want the end result
| > to be a binary file? Since SQL*Plus output is traditionally character
| > based would a column of, say, uuencoded or base64 data be better than
| > hex encoding?
|
| In the cutomer's database we have binary content stored in LOBs, the
| binary content being for example .gif files, CCD (Catia Cadam Draft) files
| (that is CAD models), MS Office documents or fractions of those, movie
files
| and so on.
|
| The customer requests a shell script (nothing compiled like C/C++) that
is
| able to issue a SQL statement that would select a particulat LOB, for
| example "SELECT CCDContents FROM CADLibrary WHERE Id=4711" (where
| CCDContents is a BLOB column and Id is the primary key) and store the
result
| (the stream of bytes that makes the LOB) in a local file on the client
| computer.
|
| With a little convincing we might get away with a Java/JDBC application
| if it is not possible otherwise. But of course I have to evaluate first
| whether it is possible otherwise, that's why I am doing.
|
| We do not expect to be the LOB size around a tera byte. The argest
| files/LOBs we have seen in our environment so far are 500MB and its not
| likely to exceed 1GB per LOB in the future. TB is just not a use case (for
| us) so I dont think about that.
|
| AH
|
|

sql*plus is the wrong tool -- it does not have the capability to extract
binary data to client files

why is the customer specifying a shell script?

what's the environment? client/server? what's the client? is sql*plus
installed on every client?

does the script need to be integrated into a system that already uses
scripts?

if you're in a web-based environment, you could also use a simple PL/SQL web
toolkit app for file upload/download -- it's fairly straight-forward,
although it places some limitations on the upload table (structure, column
names)

++ mcs