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

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:413eb8fa$1_at_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 Received on Wed Sep 08 2004 - 05:04:32 CDT