PLSQL ORA-22275: invalid LOB locator, when using CLOB/BLOB

0
votes

I havent done PLSQL for a long time and try to run a script to assist in querying a XML blob.

The script is from an old thread, (https://community.oracle.com/thread/514518) aparently worked at that time but it seems to have problems running on 11.2.

My dbs: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

CREATE OR REPLACE FUNCTION XBLOB_To_CLOB(L_BLOB BLOB) RETURN CLOB IS
L_CLOB CLOB;
L_SRC_OFFSET NUMBER;
L_DEST_OFFSET NUMBER;
L_BLOB_CSID NUMBER := DBMS_LOB.DEFAULT_CSID;
V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
L_WARNING NUMBER;
L_AMOUNT NUMBER;
BEGIN
  IF DBMS_LOB.GETLENGTH(L_BLOB) > 0 THEN
     DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
     L_SRC_OFFSET := 1;
     L_DEST_OFFSET := 1;
     L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
DBMS_LOB.CONVERTTOCLOB(L_CLOB,
  L_BLOB,
  L_AMOUNT,
  L_SRC_OFFSET,
  L_DEST_OFFSET,
  1,
  V_LANG_CONTEXT,
  L_WARNING);
RETURN L_CLOB;
  ELSE
    L_CLOB:= TO_CLOB('');
    RETURN L_CLOB;
End IF;
  DBMS_LOB.FREETEMPORARY(L_CLOB);
END;

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 ORA-06512: in "SYS.DBMS_LOB", row 978 ORA-06512: in "TEST.XBLOB_To_CLOB", row 14

Currently, the script is not executed by another script or remot function, i just tried to run it in the SQL Developer. I know that there are a lot of questions concerning ORA-22275, but i found nothing that looks like my problem. The other scripts on the thread that apparently were running well, returns the same ORA-22275 error.

Any suggestions whats wrong with the code or the dbs ?

3
oracleplsqloracle11gblobclob
How exactly are you running it? From the 'run PL/SQL' dialog when you view the function and click the green arrow, using the default boilerplate text - which passes in a null value? I don't get an error in 11.2.0.4 and SQL Developer 4.2. (You know the function name in the error message isn't quite the same as the code you posted - you're running the right function?) - Alex Poole
What is the purpose of converting a BLOB into a CLOB. For most character sets this will fail. - Wernfried Domscheit
Same here, I can run this just fine on 12.2.0.1 too. But are you sure that you are showing us the right function? Your error strack talks about TEST.BLOB2CLOB and not TEST.XBLOB_TO_CLOB - gvenzl
sorry, right function, wrong error message (but same error). I have 2 different scripts for BLOB -> CLOB that return the same ORA-22275 error. The purpose is to assist querying xml in a blob, where RAW and varchar2 run into the 2000/4000byte limit. Trying to circumvent the limit with plsql where the content limits should be much higher - Alex
You haven't said quite how you're calling it, but everything from that thread I've tries works fine in my 11.2.0.4 instance. So something more subtle is happening. Might we worth check MOS and/or raising an SR. Do you know exactly which CPU you're on, and if you've installed any patches? (There's at least one bug that causes this error that's only present if a previous patch is installed, though not convinced that particular one looks relevant). - Alex Poole

3 Answers

0
votes

How about adding the following before you check the length of the blob?

DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
0
votes

If you call proc with empty L_BLOB, then your condition goes to ELSE where you use L_CLOB but didn't define locator for it, so you should put CREATETEMPORARY out of IF ELSE block.

0
votes

You get this error when input param L_BLOB is null. For null value function GetLength returns null, then starting ELSE condition.

And in the end you try to call DBMS_LOB.FREETEMPORARY(L_CLOB); for variable which has default null-value. For null-value function FreeTemporary returns invalid LOB locator specified: ORA-22275. You should initialize L_CLOB in ELSE block or check L_CLOB before call function FreeTemporary.