ORA-22292 with DBMS_LOB

From: sboss <sbarua_at_proxicom.com>
Date: 25 May 2001 09:08:29 -0700
Message-ID: <d927adea.0105250808.597295a9_at_posting.google.com>

On Oracle 8.1.7 on Windows 2000 platform, I am trying a simple sample code to load a BFILE into a BLOB.

1.) I have created a table TEST1 as:
CREATE TABLE TEST1 (

  TEST_ID        NUMBER        NOT NULL, 
  MY_VOICE_FILE  BLOB          DEFAULT EMPTY_BLOB(), 

  CONSTRAINT TEST_PK
  PRIMARY KEY ( TEST_ID ));   2.) I have inserted the following records into the table as:

INSERT INTO TEST1 (TEST_ID) VALUES (1);
INSERT INTO TEST1 (TEST_ID) VALUES (2);
INSERT INTO TEST1 (TEST_ID) VALUES (3);

COMMIT;

• 3 records with empty blobs in MY_VOICE_FILE are seen upon SELECT.

3.) I have created a Directory as:
CREATE DIRECTORY mydir AS 'c:\test';

4.) The voice file C:\test\testsound.wav exists.

5.) I created a stored procedure as:

CREATE OR REPLACE PROCEDURE Loadlobfrombfile_Proc IS

   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('MYDIR', 'testsound.wav');

BEGIN
/* Opening the source BFILE is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);

   SELECT my_voice_file INTO Dest_loc FROM TEST1

      WHERE test_id = 2 FOR UPDATE;
/* Opening the LOB is optional: */

   DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, dbms_lob.getlength( Src_loc ) );

/* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);
   COMMIT;
END;
/

6.) When I execute the stored procedure, I get the following error:-

ORA-22292: Cannot open a LOB in read-write mode without a transaction
ORA-06512: at "SYS.DBMS_LOB", line 593
ORA-06512: at "HSWB.LOADLOBFROMBFILE_PROC", line 11
ORA-06512: at line 2

7.) I ran it in debug mode, and did notice that the Select INTO...caused a ROW SHARE mode Lock on the table TEST1. Also, V$TRANSACTION shows an active txn.

• Why am I still getting this error ?

Received on Fri May 25 2001 - 18:08:29 CEST