从plsql传出/返回的CLOB值(指定了无效的LOB定位符:ORA-22275) [英] CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)
问题描述
我已经存储了plsql过程,该过程从文件中提取大文本
I've got stored plsql procedure, that takes big text from file
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');
或更正确:
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:数字或值错误:指定了无效的LOB定位符: ORA-22275
PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
在第一种情况下,我怀疑语法不存在.
in the first case, I suspect that syntax does not exist.
如何从已存储的plsql中从使用它的过程/函数中取出/返回CLOB参数
How can I out/return CLOB parameter form the procedure/function for use it out of stored plsql
如果我有此代码
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');
然后再次出现错误
SQL错误:ORA-06502:PL/SQL:数字或值错误:指定的LOB定位器无效:ORA-22275 ORA-06512:位于"SYS.DBMS_LOB"行890中 ORA-06512:位于"VAG.DBST_LOAD_A_FILE2"的第12行
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
谢谢
推荐答案
此时:
dbms_lob.loadclobfromfile(
DEST_LOB => l_clob
...您的l_clob
OUT参数尚未初始化.将其设置为空CLOB也不起作用(因此,即使您将l_clob
设置为IN OUT参数,它仍然会抱怨)
... your l_clob
OUT parameter hasn't been initialised. Making it an empty CLOB doesn't work either (so even if you made l_clob
an IN OUT parameter it would still complain) as the documentation for empty_clob
mentions:
不能将此函数返回的定位符用作DBMS_LOB包或OCI的参数.
You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.
您可以改为使用临时CLOB,方法是在尝试使用dbms_lob.createtemporary(l_clob, true)
之前添加对其的调用:
You can use a temporary CLOB instead, by adding a call to dbms_lob.createtemporary(l_clob, true)
before you try to use it:
...
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;
/
这也可以用作功能:
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;
或者在insert
中使用,就像在您编辑的问题中一样.
Or use in an insert
as in your edited question.
这篇关于从plsql传出/返回的CLOB值(指定了无效的LOB定位符:ORA-22275)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!