oracle 从 plsql 输出/返回的 CLOB 值（指定的 LOB 定位器无效：ORA-22275）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30873543/
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
CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)
提问by Artem.Borysov
I've got stored plsql procedure, that takes big text from file
我已经存储了 plsql 程序，它从文件中获取大文本
create or replace
procedure dbst_load_a_file( p_file_name in varchar2, l_clob out clob )
as
l_bfile bfile;
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;
begin
l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadclobfromfile(
DEST_LOB => l_clob
, SRC_BFILE => l_bfile
, AMOUNT => dbms_lob.getlength( l_bfile )
, DEST_OFFSET => dst_offset
, SRC_OFFSET => src_offset
, BFILE_CSID => DBMS_LOB.DEFAULT_CSID
, LANG_CONTEXT => lang_ctx
, WARNING => warning);
dbms_lob.fileclose( l_bfile );
end;
and then I try to use it in this way:
然后我尝试以这种方式使用它：
insert into SCHEME_SOURCE (SOURCE, ID, CODE)
values (exec dbst_load_a_file( 'SCHEMES_OF_PS', 'Konotop.svg' ), 15, 'Konotop');
or more correct:
或更正确：
declare
myVal clob := empty_clob();
begin
DBMS_OUTPUT.PUT_LINE(myVal);
dbst_load_a_file('Konotop.svg', myVal);
DBMS_OUTPUT.PUT_LINE(myVal);
end;
In the second case I get an error
在第二种情况下，我收到错误
PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
PL/SQL：数字或值错误：指定的 LOB 定位器无效：ORA-22275
in the first case, I suspect that syntax does not exist.
在第一种情况下，我怀疑语法不存在。
How can I out/return CLOB parameter form the procedure/function for use it out of stored plsql
如何从过程/函数中取出/返回 CLOB 参数，以便从存储的 plsql 中使用它
If I've got this code
如果我有这个代码
create or replace
function dbst_load_a_file2( p_file_name in varchar2 ) return clob
is
l_clob clob;
l_bfile bfile;
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;
begin
l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadclobfromfile(
DEST_LOB => l_clob
, SRC_BFILE => l_bfile
, AMOUNT => dbms_lob.getlength( l_bfile )
, DEST_OFFSET => dst_offset
, SRC_OFFSET => src_offset
, BFILE_CSID => DBMS_LOB.DEFAULT_CSID
, LANG_CONTEXT => lang_ctx
, WARNING => warning);
dbms_lob.fileclose( l_bfile );
return l_clob;
end;
insert into SCHEME_SOURCE (SOURCE, ID, CODE)
values (dbst_load_a_file2('Konotop.svg' ), 15, 'Konotop');
Then again got error
然后又出错了
SQL Error: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 ORA-06512: at "SYS.DBMS_LOB", line 890 ORA-06512: at "VAG.DBST_LOAD_A_FILE2", line 12
SQL 错误：ORA-06502：PL/SQL：数字或值错误：指定的 LOB 定位器无效：ORA-22275 ORA-06512：在“SYS.DBMS_LOB”，第 890 行 ORA-06512：在“VAG.DBST_LOAD_A_FILE2”，第 12 行
Thanks
谢谢
回答by Alex Poole
At this point:
这一点：
dbms_lob.loadclobfromfile(
DEST_LOB => l_clob
... your l_clobOUT parameter hasn't been initialised. Making it an empty CLOB doesn't work either (so even if you made l_cloban IN OUT parameter it would still complain) as the documentation for empty_clobmentions:
...您的l_clobOUT 参数尚未初始化。使它成为一个空的 CLOB 也不起作用（所以即使你做了l_clob一个 IN OUT 参数它仍然会抱怨）作为empty_clob提到的文档：
You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.
您不能将此函数返回的定位器用作 DBMS_LOB 包或 OCI 的参数。
You can use a temporary CLOB instead, by adding a call to dbms_lob.createtemporary(l_clob, true)before you try to use it:
您可以使用临时 CLOB，方法是dbms_lob.createtemporary(l_clob, true)在尝试使用它之前添加对它的调用：
...
begin
l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name );
dbms_lob.fileopen( l_bfile );
dbms_lob.createtemporary(l_clob, true);
dbms_lob.loadclobfromfile(
DEST_LOB => l_clob
...
And then you don't need to give an initial empty value when you call it:
然后在调用它时不需要给出初始空值：
declare
myVal clob;
begin
dbst_load_a_file('Konotop.svg', myVal);
DBMS_OUTPUT.PUT_LINE(myVal);
end;
/
This works as a function as well:
这也可以用作函数：
create or replace
function dbst_load_a_file( p_file_name in varchar2) return clob
as
l_clob clob;
l_bfile bfile;
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;
begin
l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name );
dbms_lob.fileopen( l_bfile );
dbms_lob.createtemporary(l_clob, true);
dbms_lob.loadclobfromfile(
DEST_LOB => l_clob
, SRC_BFILE => l_bfile
, AMOUNT => dbms_lob.getlength( l_bfile )
, DEST_OFFSET => dst_offset
, SRC_OFFSET => src_offset
, BFILE_CSID => DBMS_LOB.DEFAULT_CSID
, LANG_CONTEXT => lang_ctx
, WARNING => warning);
dbms_lob.fileclose( l_bfile );
return l_clob;
end;
/
select dbst_load_a_file('Konotop.svg') from dual;
Or use in an insertas in your edited question.
或者insert在您编辑的问题中使用as 。
回答by kevinsky
You cannot use a procedure in an SQL statement, use a function instead like
您不能在 SQL 语句中使用过程，而是使用函数，例如
create or replace
FUNCTION dbst_load_a_file( p_file_name in varchar2) RETURN clob
IS
.....
END;
insert into SCHEME_SOURCE (SOURCE, ID, CODE)
values (dbst_load_a_file('SCHEMES_OF_PS'), 15, 'Konotop');
