Re: Help me with Lobs Please

A copy of this was sent to "Tom Zamani" <tom_at_taten.com.au> (if that email address didn't require changing) On Tue, 21 Sep 1999 16:03:26 +1000, you wrote:

>Could you help me with this
>I get 22288 error
>

$ oerr ora 22288
22288, 00000, "file operation %s failed\n%s"

// *Cause: The operation attempted on the file failed.
// *Action: See the next error message for more detailed information.  Also,
//          verify that the file exists and that the necessary privileges
//          are set for the specified operation.  If the error
//          still persists, report the error to the DBA.

It always helps to post an entire cut and paste of the error message. The really IMPORTANT part of the error message comes after the 22288 and we cannot see it.

I can hypothesize on the error tho. Looking at your code:

22288 typically is a file access issue. For example:

ERROR at line 1:

ORA-22288: file operation FILEOPEN failed
LFI-00108: Open expect the file [ariax][gif] to exist.
ORA-06512: at "SYS.DBMS_LOB", line 370
ORA-06512: at line 9

lets see the WHOLE error message and check to make really sure the DIRECTORY you have mapped to with 'PUBDIR' exists and is readable AND that the file you are attempting to access EXISTS and is readable.

After you fix the 22288 error, the next error you will recieve will be:

ERROR at line 1:

ORA-22990: LOB locators cannot span transactions
ORA-06512: at "SYS.DBMS_LOB", line 414
ORA-06512: at line 10

Your code should be something more like:

begin

   a_file := bfilename( 'PUBDIR', lvEdition_id||'.txt');    dbms_lob.fileopen( a_file );

   update edition set temp_clob = empty_clob()     where edition_id = lvEdition_id
   RETURNING temp_clob into ac;

   dbms_lob.loadFromFile( ac, a_file, dbms_lob.getlength(a_file) );

   dbms_lob.fileClose( a_file );

• Now that your transaction is done and over, you can COMMIT; end;

Each and every commit you do releases your LOCKS. A lob locator must be locked in order to update. Don't commit until you are DONE doing the transaction (else you leave your data messed up when you get errors).

>Thank you.
>
>CREATE OR REPLACE procedure insrt_lobs(lvEdition_ID in number default 1)
>is
>ac clob default null;
>amount number;
>a_file bfile;
>begin
>

>update edition set
> TEMP_CLOB = empty_clob()
>where EDITION_ID = lvEdition_id;
>commit;

>a_file:=bfilename('PUBDIR',lvEdition_ID||'.txt');

>select TEMP_CLOB into ac from edition where EDITION_ID = lvEdition_id for
>update;
>commit;

>dbms_lob.fileopen(a_file,dbms_lob.file_readonly);
>amount:=dbms_lob.getlength(a_file);
>dbms_lob.loadfromfile(ac,a_file,amount);
>dbms_lob.fileclose(a_file);
>dbms_output.put_line(amount);

--
See http://govt.us.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 Tue Sep 21 1999 - 07:53:12 CDT