Home » SQL & PL/SQL » SQL & PL/SQL » CLOB TO BLOB UPDATION
CLOB TO BLOB UPDATION [message #572343] Tue, 11 December 2012 00:10 Go to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Dear All,

I have a issue in updation of data from CLOB to BLOB.
Pl help me in the matter.

create or replace function CLOB_TO_BLOB (p_clob CLOB) return BLOB
as
 l_blob          blob;
 l_dest_offset   integer := 1;
 l_source_offset integer := 1;
 l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
 l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN
  DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
  DBMS_LOB.CONVERTTOBLOB
  (
   dest_lob    =>l_blob,
   src_clob    =>p_clob,
   amount      =>DBMS_LOB.LOBMAXSIZE,
   dest_offset =>l_dest_offset,
   src_offset  =>l_source_offset,
   blob_csid   =>DBMS_LOB.DEFAULT_CSID,
   lang_context=>l_lang_context,
   warning     =>l_warning
  );
  return l_blob;
END;
/
create table t (controlno number(5), col1 clob, col2 blob)
/



 DECLARE
    B_BLOB blob;
    BEGIN
       FOR T IN (SELECT controlno r FROM  x where controlno between 129 and 146)
       LOOP          
          update x  set col1 = clob_to_blob(col1) where controlno=t.r;            
       END LOOP;
       COMMIT;
   END;
SQL> /
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 "SYSADM.CLOB_TO_BLOB", line 11
ORA-06512: at line 6


Pl help me in the matter.

Regards

Anil
Re: CLOB TO BLOB UPDATION [message #572345 is a reply to message #572343] Tue, 11 December 2012 01:33 Go to previous message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It is col2 you want to set from col1, not col1.
2/ It is table t you want to update not table x.
3/ You do not need any loop for this just a single update.

Regards
Michel
Previous Topic: Inserting Single Quotes
Next Topic: mview is not refreshing
Goto Forum:
  


Current Time: Tue Sep 30 16:37:42 CDT 2014

Total time taken to generate the page: 0.11178 seconds