Home » SQL & PL/SQL » SQL & PL/SQL » Convert CLOB to BLOB
Convert CLOB to BLOB [message #257785] Thu, 09 August 2007 03:05 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I have two tables and I convert one table data(CLOB)and put it to another table data(BLOB). How it can be achieved? I proceed in this way.How I will fix it?


SQL> desc c_lob
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEXT                                               CLOB

SQL> desc blob_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEXT                                               BLOB

SQL>  declare
    v_blob Blob;
    v_clob Clob;
    v_in Pls_Integer := 1;
    v_out Pls_Integer := 1;
    v_lang Pls_Integer := 0;
    v_warning Pls_Integer := 0;
    begin
   select text into v_clob from c_lob;
  DBMS_LOB.convertToBlob(v_blob, v_clob, DBMS_LOB.getlength(v_clob), v_in, v_out,DBMS_LOB.default_csid,
  v_lang, v_warning);
   insert into blob_test values(v_blob);
  end;  2    3    4    5    6    7    8    9   10   11   12   13
 14  /
 declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 696
ORA-06512: at line 10

[Updated on: Thu, 09 August 2007 03:05]

Report message to a moderator

Re: Convert CLOB to BLOB [message #257792 is a reply to message #257785] Thu, 09 August 2007 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first create a (empty) blob before assigning it with the dbms_lob procedure.

Regards
Michel
Re: Convert CLOB to BLOB [message #257800 is a reply to message #257785] Thu, 09 August 2007 03:28 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes after inserting empty_blob() I ran the procedure.
Re: Convert CLOB to BLOB [message #257832 is a reply to message #257785] Thu, 09 August 2007 05:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Will you please make a test in your end where I am wrong?
Re: Convert CLOB to BLOB [message #257870 is a reply to message #257785] Thu, 09 August 2007 07:03 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I think what Michel is trying to lead you to is you need to use dbms_lob.createtemporary in your pl/sql.

Re: Convert CLOB to BLOB [message #258432 is a reply to message #257785] Sat, 11 August 2007 22:57 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thanks. Done it.
By
dbms_lob.createtemporary(v_blob, TRUE);
Previous Topic: pivot concept doubt
Next Topic: Reg:Hierachical Trees
Goto Forum:
  


Current Time: Sat Dec 10 03:00:12 CST 2016

Total time taken to generate the page: 0.17635 seconds