CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)

Question

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');

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

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

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

Thanks

Solution 1

At this point:

    dbms_lob.loadclobfromfile(
      DEST_LOB     => l_clob

... 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:

You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the 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:

...
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 insert as in your edited question.

Solution 2

You cannot use a procedure in an SQL statement, use a function instead like

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');

Comments

[Artem.Borysov]

Good idea, I'll try it now. if it doesn't help then the suspicion will remain on the specifics of working with clob cursor

[Alex Poole]

True, but not relevant to the ORA-22275 error when called from a PL/SQL block; and the same error will be thrown with the code converted to a function.

[Artem.Borysov]

adding a one row to plsql ( dbms_lob.createtemporary(l_clob, true); ) make all fine! I add it to function(not procedure) variant between strokes [ dbms_lob.fileopen( l_bfile ); ] and [ dbms_lob.loadclobfromfile( ] as you have shown

[Artem.Borysov]

Add your function realisation to the end of the question