Re: BLOB, CLOB

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 6 May 2002 15:38:28 +0400
Message-ID: <ab5pvn$mev$1@babylon.agtel.net>

One simple example is a PL/SQL function that hides the complexity of creating temporary LOB locator and filling the LOB with data inside and returns a locator to already populated LOB. For example:

create or replace function getFileImage( filename varchar2 ) return BLOB is

rv BLOB;
f BFILE := BFileName('some_dir', filename); begin

   dbms_lob.createTemporary(rv, true, dbms_lob.call);
   dbms_lob.fileOpen(f, dbms_lob.file_readonly);
   dbms_lob.loadFromFile(rv, f, dbms_lob.getLength(f));

return rv;

no exception handling here, we don't want to mask 'file not found' errors end; /

The following insert will work perfectly:

insert into lob_table ( name, content ) values( 'some file.ext', getFileImage('some file.ext') )

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3CD5318E.B08C61CE_at_exesolutions.com...


> Niall Litchfield wrote:

>

> > look into dbms_lob which will allow you to do this .

> >

> > --

> > Niall Litchfield

> > Oracle DBA

> > Audit Commission UK

> > "Simon Walchshofer" <simon.walchshofer_at_students.uni-linz.ac.at> wrote in

> > message news:3cd530d1_at_news.uni-linz.ac.at...

> > > Hi!

> > >

> > > Is it possible to insert for example a picture into a table with one

> > > sql-insert statement?

> > >

> > > e.g.

> > > create table myLob (id number, picture clob);

> > >

> > > insert into myLob values (1, ??????????);

> > >

> > > Is there a possibility to specify the file-path directly in the

> > > insert-statement??

> > >

> > > THX

> > > Simon

> > >

> > >

>

> Please correct me if I am wrong but it is not possible, DBMS_LOB, or not.

> Every time I have gotten it to work I have inserted a row with an

> EMPTY_BLOB() reutrning ROWID and then locked it then updated it with the

> image.

>

> I would be interested in seeing an example that bypasses that requirement.

>

> Daniel Morgan

>

Received on Mon May 06 2002 - 06:38:28 CDT