Home » SQL & PL/SQL » SQL & PL/SQL » ConvertToClob
ConvertToClob [message #187892] Wed, 16 August 2006 04:38 Go to next message
tom01
Messages: 29
Registered: July 2006
Junior Member
Hi!

I typed:

DECLARE
temp_blob BLOB;
temp_clob CLOB;
amount integer :=2000;
dest_offset number(3) :=1;
src_offset number(3) :=1;
default_lang_ctx integer := 0;
warning integer :=1;

BEGIN
...
dbms_lob.converttoclob(temp_clob,temp_blob,amount,dest_offset,src_offset,190,default_lang_ctx,warning);
...
END;


The error:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275


What is wrong??

Best regards,
Tom
Re: ConvertToClob [message #187933 is a reply to message #187892] Wed, 16 August 2006 07:01 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Remember to initialize your lobs:

SQL> DECLARE
  2    temp_blob   BLOB;
  3    temp_clob   CLOB;
  4    dest_offset NUMBER  := 1;
  5    src_offset  NUMBER  := 1;
  6    amount      INTEGER := dbms_lob.lobmaxsize;
  7    blob_csid   NUMBER  := dbms_lob.default_csid;
  8    lang_ctx    INTEGER := dbms_lob.default_lang_ctx;
  9    warning     INTEGER;
 10  BEGIN
 11    DBMS_LOB.CREATETEMPORARY(lob_loc=>temp_blob, cache=>TRUE, dur=>dbms_lob.SESSION);
 12    DBMS_LOB.CREATETEMPORARY(lob_loc=>temp_clob, cache=>TRUE, dur=>dbms_lob.SESSION);
 13
 14    DBMS_LOB.CONVERTTOCLOB(temp_clob,temp_blob,amount,dest_offset,src_offset,blob_csid,lang_ctx,warning);
 15  END;
 16  /

PL/SQL procedure successfully completed.
Re: ConvertToClob [message #188335 is a reply to message #187933] Fri, 18 August 2006 02:16 Go to previous messageGo to next message
tom01
Messages: 29
Registered: July 2006
Junior Member
Yeah, exactly, I forgot about createtemporary...

Thanks!
Re: ConvertToClob [message #239017 is a reply to message #187933] Mon, 21 May 2007 04:57 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Hi,
I have to convert blob to clob. I saw your code. How to use that one means in that code not used any table .I given my problem below give me guidence:
I saving docs(word format) in BLOB column. I have to convert that data to clob.give me idea.
Thanks
Re: ConvertToClob [message #239036 is a reply to message #239017] Mon, 21 May 2007 05:40 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a badidea to convert Word document from BLOB to CLOB.
If you do so then you will no more have a valid Word document and so loose your data.

Regards
Michel
Previous Topic: Monitoring Database changes
Next Topic: Stored Procedure in oracle
Goto Forum:
  


Current Time: Thu Mar 28 20:17:08 CDT 2024