How to save a BLOB to a file on disk in PL/SQL (From another thread)

markyg_7_at_yahoo.co.uk (SoulSurvivor) wrote in message news:<8d9c6fd.0301280120.72e9e0c8_at_posting.google.com>...

Let's shrink this thread ONLY to comp.database.oracle.misc and spare everyone else the agony.

> Disagree strongly.

Ok... "Have no choice" was a bit too strong. I should have known someone was going to come back with Extproc, OCI etc.

BTW... OP *Only* wants to do it with built in Plsql packages. So in that case he is limited to 9i.

<SNIP>

> You will need to create an external procedure to take binary data and
> write it to the operating system, the external procedure can be
> written in C. If it was CLOB data, you can use UTL_FILE to write it
> to the OS but UTL_FILE does not support the binary in a BLOB.
>
> There are articles on MetaLink explaining how to do and it has a C
> program ready for compiling and the External Procedure stuff, i'd
> advise a visit.
> Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE
> CONTENTS TO A FILE USING EXTERNAL PROCEDURES
<SNIP>

Here is my take on it. It's very trivial to create io streams in java and since 8i, you can do it right inside the database. Big advantage of using java is that you have the JVM built into the database. Your once compiled java class will run no matter which platform you run Oracle on. C External library, on the other hand, relies on OS and linked files that reside outside the database...unless you recompile to port to a different platform...you are toast.

Other downside to using C extprocs (For average Plsql programmer who is not familiar with it) is that you have to know C atleast on medium to advanced level to create a descent external library + you need a C compiler. Learning curve for java is much smaller then C.

IMHO... If comparing C and Java (Only in the context of Oracle external procedures) since release 8i, I can safely say that C external procedures are pretty much dinosaurs. You can do all that in Java stored procedures in no time.

I am not against C or trying to compare java with C. They are both tools to do a certain job and I think in this instance, java is the right tool.

Regards
/Rauf Sarwar Received on Tue Jan 28 2003 - 10:35:22 CST