Oracle varchar2 into Clob and blob__oracle

The 2-table field VARCHAR2 (4000) Now expands the length, but continues to expand the error, which has reached the maximum length of the VARCHAR2, so the plan will need to change from varchar2 to blob;


Current 2-table field types
ALTER TABLE TECHINFOA Modify (techd VARCHAR2 (4000));
ALTER TABLE KNOWLEDGEP Modify (pcontent VARCHAR2 (4000));




method One: Through the Utl_raw.cast_to_raw implementation:

ALTER TABLE TECHINFOA add techd_b blob;
Update Techinfoa set Techd_b=utl_raw.cast_to_raw (TECHD);
Commit
ALTER TABLE TECHINFOA drop Colum techd;
ALTER TABLE TECHINFOA rename techd_b to Techd;


sql> Update Techinfoa set Techd_b=utl_raw.cast_to_raw (TECHD);
Update Techinfoa set Techd_b=utl_raw.cast_to_raw (TECHD)
*
Line 1th Error:
Ora-06502:pl/sql: Number or value error: Raw variable length too long
ORA-06512: In the SYS. Utl_raw ", line 224




A RAW is limited to bytes. But the value in this table is already over 2000, so this method is not feasible,


method Two: Solve by To_clob and Dbms_lob

The basic idea is to convert the VARCHAR2 to CLOB, and then convert the Clob to a blob.




Next is the procedure, the function used will be given in the following: (Write 2 function:c2b v2b, the function is to change the clob into a blob, the VARCHAR2 will be a blob)


table Techinfoa Conversion, with a functionc2bALTER TABLE TECHINFOA add techd_b blob;
ALTER TABLE TECHINFOA add Techd_c clob;
Update Techinfoa set Techd_c=to_clob (TECHD);
Update Techinfoa set TECHD_B=C2B (Techd_c);
Commit
ALTER TABLE TECHINFOA drop column techd;
ALTER TABLE TECHINFOA Rename column techd_b to Techd;
ALTER TABLE TECHINFOA drop column Techd_c;




table KNOWLEDGEP conversion, using a function v2bALTER TABLE KNOWLEDGEP add Pcontent_b BLOB;
Update KNOWLEDGEP set PCONTENT_B=V2B (pcontent);
Commit
ALTER TABLE KNOWLEDGEP drop column pcontent;
ALTER TABLE KNOWLEDGEP Rename column pcontent_b to pcontent;


There are two of them that are not functional and can be used.




2 function used


Function Reference: http://blog.csdn.net/wbo112/article/details/9041575


CREATE OR REPLACE FUNCTION c2b (c_clob in Clob default Empty_clob ())
Return BLOB
Is
B_blob blob;
B_len Number: = Dbms_lob.getlength (C_clob);
Dest_offset1 number: = 1;
Src_offset1 number: = 1;
Amount_c INTEGER: = dbms_lob.lobmaxsize;
Blob_csid number: = Dbms_lob.default_csid;
Lang_ctx INTEGER: = Dbms_lob.default_lang_ctx;
Warning INTEGER;
BEGIN


If B_len > 0 Then
Dbms_lob.createtemporary (B_blob, TRUE);
Dbms_lob. OPEN (B_blob, dbms_lob.lob_readwrite);
Dbms_lob.converttoblob (B_blob,
C_clob,
Amount_c,
Dest_offset1,
Src_offset1,
Blob_csid,
Lang_ctx,
Warning
);
Else
Select Empty_blob () into the b_blob from dual;
End If;
return b_blob;
End C2B;




CREATE OR REPLACE FUNCTION v2b (L_var in varchar2) return BLOB is
C_clob Clob: = To_clob (L_var);
B_blob blob;
B_len Number: = Dbms_lob.getlength (C_clob);
B_offset number: = 1;
C_offset number: = 1;
C_amount INTEGER: = dbms_lob.lobmaxsize;
B_csid number: = Dbms_lob.default_csid;
Lang_ctx INTEGER: = Dbms_lob.default_lang_ctx;
Warning INTEGER;
BEGIN
If B_len > 0 Then
Dbms_lob.createtemporary (B_blob, TRUE);
Dbms_lob. OPEN (B_blob, dbms_lob.lob_readwrite);
Dbms_lob.converttoblob (B_blob,
C_clob,
C_amount,
B_offset,
C_offset,
B_csid,
Lang_ctx,
Warning);
Else
Select Empty_blob () into the b_blob from dual;
End If;
return b_blob;
End v2b;