oracle 出现错误 ORA-22275: 指定的 LOB 定位器无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23649160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting error ORA-22275: invalid LOB locator specified
提问by user3098484
I am new to LOB data type, i am getting error will execute this procedure script as below....
我是 LOB 数据类型的新手，我收到错误将执行此过程脚本如下....
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
将执行以下脚本获取错误消息 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;
回答by Egor Skriptunoff
V_RESULT2becomes invalid locator after invocation of xml_zip_pkg_2.ZIP(V_Result,V_RESULT2);because of DBMS_LOB.FREETEMPORARY(dst);inside ZIP
V_RESULT2xml_zip_pkg_2.ZIP(V_Result,V_RESULT2);由于DBMS_LOB.FREETEMPORARY(dst);内部原因，调用后成为无效定位器ZIP
回答by Alex Poole
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:
在调用者有机会对其进行任何操作之前，您正在释放 BLOB。但是您实际上也不需要创建它，因为它是由调用者声明的 - 如果参数是OUT，您只需要创建它，而不是IN OUT. 所以你的程序只需要做：
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:
调用者可以（并且应该）在使用 BLOB 后仍然释放它：
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:
或者在循环之后。在循环之前在调用者中创建一次临时 BLOB 并在最后释放它可能更有效；那么您的程序可以执行以下操作：
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_Stronce, and this doesn't need to use dynamic SQL at all:
虽然你可以定义V_Query_Str一次，而且这根本不需要使用动态SQL：
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 converttoblobas we don't have your package.
这是一个 SQL Fiddle 演示，使用，converttoblob因为我们没有你的包。
