CODEWITHSUNDEEP
oracle-database
user3098484
user3098484

Reputation: 83

Getting error ORA-22275: invalid LOB locator specified

I am new to LOB data type, i am getting error will execute this procedure script as below....

CREATE TABLE SA_ZIP_TEST (
   SEQ          NUMBER,
   STRING_BLOB  BLOB,
   STRING_CLOB  CLOB,
   DT           DATE
);

CREATE OR REPLACE PROCEDURE ZIP(src IN clob, dst IN OUT NOCOPY BLOB )
IS
BEGIN
   IF src is not null THEN
      DBMS_LOB.createtemporary(dst, true, DBMS_LOB.CALL);
      XML_ZIP_PKG_2.ZIP_CLOB(src, dst);
      DBMS_LOB.FREETEMPORARY(dst);
   END IF;
END ZIP;

Will execute the below script getting an error message ORA-22275

DECLARE
   V_Query_Str VARCHAR2 (4000);
   V_RESULT CLOB; 
   V_RESULT2 bLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(V_Result, TRUE, dbms_lob.session);
   FOR I IN 1..100
   LOOP
      V_Query_Str:=
         'INSERT INTO SA_ZIP_TEST
            (seq
            ,DT
            ,STRING_BLOB
            )
         VALUES
            (:Cmd_Seq
            ,:Cmd_DT 
            ,:Result)
      ';
      xml_zip_pkg_2.ZIP(V_Result,V_RESULT2);
      EXECUTE IMMEDIATE V_Query_Str USING I, SYSDATE,V_RESULT2 ;
   END LOOP;
END;

Upvotes: 0

Views: 25958

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You are freeing the BLOB before the caller has a chance to do anything with it. But you don't need to create it either really, since it's declared by the caller - you would only need to create it if the parameter was OUT, rather than IN OUT. So your procedure only needs to do:

   IF src is not null THEN
      DBMS_LOB.createtemporary(dst, true, DBMS_LOB.CALL);
      XML_ZIP_PKG_2.ZIP_CLOB(src, dst);
   END IF;

The caller can (and should) still free the BLOB after using it:

      ZIP(V_Result,V_RESULT2);
      EXECUTE IMMEDIATE V_Query_Str USING I, SYSDATE,V_RESULT2 ;
      DBMS_LOB.FREETEMPORARY(V_RESULT2);

Or after the loop. It might be more efficient to create the temporary BLOB once in the caller, before the loop, and free it at the end; then your procedure can do:

   IF src is not null THEN
      XML_ZIP_PKG_2.ZIP_CLOB(src, dst);
   END IF;

And the caller can do:

BEGIN
   DBMS_LOB.CREATETEMPORARY(V_Result, TRUE, dbms_lob.session);
   DBMS_LOB.CREATETEMPORARY(V_Result2, TRUE, dbms_lob.call);
   FOR I IN 1..100
   LOOP
      ..
      ZIP(V_Result,V_RESULT2);
      EXECUTE IMMEDIATE V_Query_Str USING I, SYSDATE,V_RESULT2 ;
   END LOOP;
   DBMS_LOB.FREETEMPORARY(V_RESULT2);
END;

Although you can define V_Query_Str once, and this doesn't need to use dynamic SQL at all:

DECLARE
   V_RESULT CLOB; 
   V_RESULT2 bLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(V_Result, TRUE, dbms_lob.session);
   DBMS_LOB.CREATETEMPORARY(V_Result2, TRUE, dbms_lob.call);
   FOR I IN 1..100 LOOP
      ZIP(V_Result,V_RESULT2);
      INSERT INTO SA_ZIP_TEST
         (seq
          ,DT
           ,STRING_BLOB
           )
         VALUES
           (i
           ,sysdate 
           ,V_RESULT2);
   END LOOP;
   DBMS_LOB.FREETEMPORARY(V_RESULT2);
END;
/

Here's an SQL Fiddle demo, using converttoblob as we don't have your package.

Upvotes: 2

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

V_RESULT2 becomes invalid locator after invocation of xml_zip_pkg_2.ZIP(V_Result,V_RESULT2); because of DBMS_LOB.FREETEMPORARY(dst); inside ZIP

Upvotes: 3

Related Questions