Re: DBMS_LOB Part II

Yeah, what you are trying to do is make LOB operations span transactions and you're not allowed to do that. You get a lob locator for the transaction only - once you close the transaction you lose the locator.

Thomas Kyte wrote:

> A copy of this was sent to Henry Katz <hkatz_at_iscs-i.com>
> (if that email address didn't require changing)
> On Wed, 10 Nov 1999 09:00:06 -0500, you wrote:
>
> >mgumbs wrote:
> >
> >> Hi,
> >>
> >> I have managed to read a character document into the DB using DBMS_LOB.
> >> Is there a way to extract the entire document out again using DBMS_LOB
> >> without doing it line by line without using UTL_FILE?
> >
> >Mark,
> >
> >Can I ask you how you accomplished the first part? I'm encountering a slight
> >error:
> >
> >SQL> exec lobops.loadLOBFromBFILE(1,1);
> >BEGIN lobops.loadLOBFromBFILE(1,1); END;
> >
> >*
> >ERROR at line 1:
> >ORA-22292: Cannot open a LOB in read-write mode without a transaction
> >ORA-06512: at "SYS.DBMS_LOB", line 593
> >ORA-06512: at "PPIXADM.LOBOPS", line 81
> >ORA-06512: at line 1
> >
> >and here's my proc (this is under 8.1.5):
> >
> >PROCEDURE loadLOBFromBFILE(blobnum IN INTEGER,
> > num IN INTEGER) IS
> >Dest_loc BLOB;
> >Src_loc BFILE;
> >Amount INTEGER;
> >BEGIN
> >-- SET TRANSACTION READ WRITE;
> > SELECT image INTO Dest_loc FROM images
> > WHERE blobid = blobnum
> > FOR UPDATE;
> >
> > SELECT src INTO Src_loc FROM image_src
> > WHERE seq = num;
> >
> > /* Opening the LOB is mandatory: */
> > DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
> > DBMS_OUTPUT.PUT_LINE('OK 1');
> >
> > COMMIT;
> >
>
> why did you commit? thats the problem. to write to a lob you must have it
> locked. you locked it with the for update but then you gave up the lock with
> the commit.
>
> Here is a small'ish example that shows how to do this simply:
>
> declare
> l_blob blob;
> l_bfile bfile;
> begin
> insert into demo values ( 1, empty_blob() )
> returning theBlob into l_blob;
>
> l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
> dbms_lob.fileopen( l_bfile );
>
> dbms_lob.loadfromfile( l_blob, l_bfile,
> dbms_lob.getlength( l_bfile ) );
>
> dbms_lob.fileclose( l_bfile );
> commit;
> end;
> /
>
> you can replace the insert with a SELECT for update or an UPDATE of an existing
> row.
>
> > /* Opening the LOB is optional: */
> > DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
> > DBMS_OUTPUT.PUT_LINE('OK 2');
> >
> > Amount := DBMS_LOB.GETLENGTH(Src_loc);
> > DBMS_OUTPUT.PUT_LINE('OK 3');
> >-- DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
> > Amount := 500;
> > DBMS_OUTPUT.PUT_LINE('OK 4');
> >
> > /* Closing the LOB is mandatory if you have opened it: */
> > DBMS_LOB.CLOSE(Dest_loc);
> > DBMS_OUTPUT.PUT_LINE('OK 5');
> > DBMS_LOB.CLOSE(Src_loc);
> > DBMS_OUTPUT.PUT_LINE('OK 6');
> > COMMIT;
> >END;
> >
> >I thought that the update and commit lines would encapsulate my transaction.
> >This is nearly literally
> >out of the App Dev - LOB manual.
> >
> >TIA,
> >Henry Katz
> >
> >ISCS, Inc
> >hkatz_at_iscs-i.com
> >
> >
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Fri Nov 12 1999 - 15:01:01 CST