Deadlock when using DBMS_LOB

From: <gorbead_at_mailserver.volvo.com>
Date: Wed, 24 Jan 2001 10:56:14 -0500
Message-Id: <10751.127369@fatcity.com>

Hi List Members,

I'm using EXECUTE IMMEDIATE to create a temporary table for XML related data. However, when I go to drop
the table following the COMMIT, "ORA-04020 deadlock detected..." is issued for the temporary table.
The DBMS_LOB.LOADFROMFILE procedure seems to be causing the problem. For example, the following
code without the DBMS_LOB.LOADFROMFILE procedure executes withou a hitch:

begin

        SELECT TO_CHAR(sysdate,'YYYYMMDDHHMISS') INTO vTabSuffix FROM dual;

        vSQLStmt := 'CREATE GLOBAL TEMPORARY TABLE test_sql_'||vTabSuffix||


        ' (xml_sql_string CLOB) ON COMMIT DELETE ROWS';


        EXECUTE IMMEDIATE vSqlStmt;

        vSQLStmt := 'INSERT INTO test_sql_'||vTabSuffix||' VALUES (:1)
                     RETURNING xml_sql_string INTO :2';

        EXECUTE IMMEDIATE vSQLStmt USING vInitClob RETURNING INTO vSQLClob;

        EXECUTE IMMEDIATE 'COMMIT WORK';

        vSQLStmt := 'DROP TABLE test_sql_'||vTabSuffix;

        EXECUTE IMMEDIATE vSQLStmt;

end;

However, when the DBMS_LOB procedure is introduced, as in the following code, the deadlock occurs:

begin

        SELECT TO_CHAR(sysdate,'YYYYMMDDHHMISS') INTO vTabSuffix FROM dual;

        vSQLStmt := 'CREATE GLOBAL TEMPORARY TABLE temp_xml_sql_'||vTabSuffix||

' (xml_sql_string CLOB) ON COMMIT DELETE ROWS';

        EXECUTE IMMEDIATE vSqlStmt;

        vSQLStmt := 'INSERT INTO temp_xml_sql_'||vTabSuffix||' VALUES (:1)
                     RETURNING xml_sql_string INTO :2';

        EXECUTE IMMEDIATE vSQLStmt USING vInitClob RETURNING INTO vSQLClob;

	vSQLFile := BFILENAME('TEST_XML_DIR','df_dealer.sql');

       dbms_lob.fileopen(vSQLFile);
        vSQLFileLng := dbms_lob.GETLENGTH(vSQLFile);

	dbms_lob.loadfromfile(dest_lob => vSQLClob,
			      src_lob  => vSQLFile,
			      amount   => vSQLFileLng);

        dbms_lob.fileclose(vSQLFile);

	vXMLClob := xmlgen.getXML(vSQLClob);

        insert into xml_doc values (xml_doc_seq.nextval,'DEALERS

TEST',SYSDATE,vXMLClob);

        EXECUTE IMMEDIATE 'COMMIT';         vSQLStmt := 'DROP TABLE temp_xml_sql_'||vTabSuffix;

        EXECUTE IMMEDIATE vSQLStmt;
end;

The DBMS_LOB.LOADFROMFILE is merely loading an O/S SQL script into a CLOB column in the temporary table. With the exception of the DDL for dropping the table, all of the code works okay.

Any help with this would be much appreciated.

Regards,

Dale

Dale Gorbea
Database Services Manager
Volvo Commercial Finance LLC The Americas Received on Wed Jan 24 2001 - 09:56:14 CST