Home » SQL & PL/SQL » SQL & PL/SQL » Error in converting CLOB to BLOB (SQL*Plus: Release 11.2.0.1.0)
Error in converting CLOB to BLOB [message #671383] Mon, 27 August 2018 13:59 Go to next message
vinodkumarn
Messages: 58
Registered: March 2005
Member
Hello,

I have a function to convert CLOB to BLOB and getting error, not sure what the problem is. I was using the same code earlier successfully and now I am getting error for a different dataset

Below is the function code


create or replace FUNCTION CLOB_To_BLOB1 (p_CLOB IN CLOB) RETURN BLOB
AS
v_BLOB BLOB;
v_RAW RAW(32767);
v_Start PLS_INTEGER := 1;
v_Buffer PLS_INTEGER := 32767;
v_CLOB_Len NUMBER;
v_Lob_Locator BLOB := EMPTY_BLOB();
v_Loop_Ceil NUMBER(10,4);
xmllength long;
BEGIN
--If the Length of the CLOB is 0( NULL Clob then return)
xmllength := length(p_CLOB);
dbms_output.put_line('p_CLOB length = '||xmllength);
v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);

dbms_output.put_line('Package p_CLOB length = '||v_CLOB_Len);

IF NVL(v_CLOB_len,0) = 0
THEN
DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);
v_Lob_Locator := v_BLOB ;
RETURN v_Lob_Locator;
END IF;
----------------------------------------------------------------------------------
--If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth
IF v_CLOB_Len < 32767
THEN
v_Buffer := v_CLOB_len;
ELSE
v_Buffer := 32767;
END IF;

dbms_output.put_line('Package v_Buffer1 length = '||v_Buffer);
----------------------------------------------------------------------------------
DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);
v_Loop_Ceil:=v_CLOB_Len/v_Buffer;
dbms_output.put_line('Package v_Loop_Ceil length = '||v_Loop_Ceil);

FOR i IN 1..CEIL(v_Loop_Ceil)
LOOP
dbms_output.put_line('inside loop1');
v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));
dbms_output.put_line('inside loop2');
DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);
dbms_output.put_line('inside loop3');
v_Start := v_Start + v_Buffer;
dbms_output.put_line('inside loop4');
v_Buffer := v_clob_len - v_Start + 1 ;
dbms_output.put_line('Package v_Buffer2 length = '||v_Buffer);
IF (v_Buffer > 32767)
THEN
dbms_output.put_line('Package v_Buffer3 length = '||v_Buffer);
v_Buffer := 32767;
END IF;
END LOOP;

v_Lob_Locator := v_BLOB;
DBMS_LOB.FREETEMPORARY(v_BLOB);
RETURN v_Lob_Locator;
END CLOB_To_BLOB1;


ERROR

v_xml_clob length = 65702
p_CLOB length = 65702
Package p_CLOB length = 65702
Package v_Buffer1 length = 32767
Package v_Loop_Ceil length = 2.0051
inside loop1
inside loop2

Error report -
ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 1139
ORA-06512: at "ELCREVEL.CLOB_TO_BLOB1", line 45
21560. 00000 - "argument %s is null, invalid, or out of range"


I get the below error when I comment the line "DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);"

v_xml_clob length = 65702
p_CLOB length = 65702
Package p_CLOB length = 65702
Package v_Buffer1 length = 32767
Package v_Loop_Ceil length = 2.0051
inside loop1
inside loop2

Error report -
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1139
ORA-06512: at "ELCREVEL.CLOB_TO_BLOB1", line 45
06502. 00000 - "PL/SQL: numeric or value error%s"
Re: Error in converting CLOB to BLOB [message #671384 is a reply to message #671383] Mon, 27 August 2018 16:28 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

How can we reproduce what you report?
Re: Error in converting CLOB to BLOB [message #671385 is a reply to message #671384] Mon, 27 August 2018 20:34 Go to previous messageGo to next message
vinodkumarn
Messages: 58
Registered: March 2005
Member
Hello,

I have tried my best to align the code and the link on how to use tags to make your code easier to read is not working

I have a function to convert CLOB to BLOB and getting error, not sure what the problem is. I was using the same code earlier successfully and now I am getting error for a different dataset

Below is the function code


create or replace FUNCTION CLOB_To_BLOB1 (p_CLOB IN CLOB) RETURN BLOB
AS
   v_BLOB	BLOB;
   v_RAW	RAW(32767);
   v_Start	PLS_INTEGER := 1;
   v_Buffer	PLS_INTEGER := 32767;
   v_CLOB_Len	NUMBER;
   v_Lob_Locator BLOB := EMPTY_BLOB();
   v_Loop_Ceil	NUMBER(10,4);
   xmllength    long;
BEGIN
     --If the Length of the CLOB is 0( NULL Clob then return) 
     xmllength := length(p_CLOB);
     dbms_output.put_line('p_CLOB length = '||xmllength); 
     v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);

     dbms_output.put_line('Package p_CLOB length = '||v_CLOB_Len);

     IF NVL(v_CLOB_len,0) = 0 THEN
       DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);
       v_Lob_Locator := v_BLOB ;
       RETURN	v_Lob_Locator;
     END IF;

     --If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth
     IF v_CLOB_Len < 32767 THEN
        v_Buffer := v_CLOB_len;
     ELSE
        v_Buffer := 32767;
     END IF;

     dbms_output.put_line('Package v_Buffer1 length = '||v_Buffer);
     ----------------------------------------------------------------------------------
     DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);
     v_Loop_Ceil:=v_CLOB_Len/v_Buffer; 
     dbms_output.put_line('Package v_Loop_Ceil length = '||v_Loop_Ceil);

     FOR i IN 1..CEIL(v_Loop_Ceil) LOOP
        dbms_output.put_line('inside loop1');
        v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));
        dbms_output.put_line('inside loop2');
        DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);
        dbms_output.put_line('inside loop3');
        v_Start := v_Start + v_Buffer;
        dbms_output.put_line('inside loop4');
        v_Buffer := v_clob_len - v_Start + 1 ;
        dbms_output.put_line('Package v_Buffer2 length = '||v_Buffer);

        IF (v_Buffer > 32767) THEN
           dbms_output.put_line('Package v_Buffer3 length = '||v_Buffer);
           v_Buffer := 32767; 
        END IF;
     END LOOP;

     v_Lob_Locator := v_BLOB;
     DBMS_LOB.FREETEMPORARY(v_BLOB);
     RETURN v_Lob_Locator;
END CLOB_To_BLOB1;


ERROR

v_xml_clob length = 65702
p_CLOB length = 65702
Package p_CLOB length = 65702
Package v_Buffer1 length = 32767
Package v_Loop_Ceil length = 2.0051
inside loop1
inside loop2

Error report -
ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 1139
ORA-06512: at "ELCREVEL.CLOB_TO_BLOB1", line 45
21560. 00000 - "argument %s is null, invalid, or out of range"


I get the below error when I comment the line "DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);"

v_xml_clob length = 65702
p_CLOB length = 65702
Package p_CLOB length = 65702
Package v_Buffer1 length = 32767
Package v_Loop_Ceil length = 2.0051
inside loop1
inside loop2

Error report -
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1139
ORA-06512: at "ELCREVEL.CLOB_TO_BLOB1", line 45
06502. 00000 - "PL/SQL: numeric or value error%s"
Re: Error in converting CLOB to BLOB [message #671386 is a reply to message #671385] Mon, 27 August 2018 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Yes, you have an error.
How can we reproduce what you report?
Which line in your code throws the error?
It appears to be a data dependent error so without your data we can produce same error.
Re: Error in converting CLOB to BLOB [message #671387 is a reply to message #671386] Mon, 27 August 2018 22:11 Go to previous messageGo to next message
vinodkumarn
Messages: 58
Registered: March 2005
Member
Okay. I have attached a sql file called "code to reproduce error.sql". It has below 3 parts. Sorry for the incomplete message earlier.

1) 1st part -- To create the concerned tables and insert records
2) 2nd part ---- Function to convert clob to blob
3) 3rd part ----- Procedure which generates an xml and converts into clob, and then calls above function to convert to blob

which is in when i am getting the following error



p_CLOB length = 65702
Package p_CLOB length = 65702
Package v_Buffer1 length = 32767
Package v_Loop_Ceil length = 2.0051
inside loop1
inside loop2


Error starting at line : 1 in command -
BEGIN je_publish; END;
Error report -
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1139
ORA-06512: at "ELCREVEL.CLOB_TO_BLOB", line 45
ORA-06512: at "ELCREVEL.JE_PUBLISH", line 53
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
Re: Error in converting CLOB to BLOB [message #672341 is a reply to message #671387] Thu, 11 October 2018 23:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8898
Registered: November 2002
Location: California, USA
Senior Member
I tested the code that you provided and received the same results. I then tried replacing your function with the one below and it appears to work. Please try replacing your clob_to_blob function with the following that uses the dbms_lob.converttoblob procedure.

CREATE OR REPLACE FUNCTION clob_to_blob
  (p_clob      IN CLOB)
  RETURN          BLOB
AS
  v_blob          BLOB;
  v_dest_offset   INTEGER := 1;
  v_src_offset    INTEGER := 1;
  v_lang_context  INTEGER := 0;
  v_warning       INTEGER := 0;
  v_lob_locator   BLOB := EMPTY_BLOB ();
BEGIN
    DBMS_LOB.CREATETEMPORARY (v_blob, TRUE, DBMS_LOB.SESSION);  
    DBMS_LOB.CONVERTTOBLOB 
      (v_blob, 
       p_clob,
       DBMS_LOB.LOBMAXSIZE, 
       v_dest_offset, 
       v_src_offset, 
       DBMS_LOB.DEFAULT_CSID, 
       v_lang_context, 
       v_warning);
    v_lob_locator := v_blob;
    DBMS_LOB.FREETEMPORARY (v_blob);
    RETURN v_lob_locator;
END clob_to_blob;
/

Re: Error in converting CLOB to BLOB [message #672370 is a reply to message #672341] Fri, 12 October 2018 11:55 Go to previous message
vinodkumarn
Messages: 58
Registered: March 2005
Member
It works. Thank you Barbara.
Previous Topic: Return Rows when no data exists
Next Topic: Complex SQL Quiries for me
Goto Forum:
  


Current Time: Mon Oct 22 18:21:09 CDT 2018