oracle blob 转 varchar2,oracle pl/sql clob转换为blob（clob2blob），blob转换为clob(blob2clob)方法...

程序一：oracle clob转换为blob(clob2blob)

PROCEDURE clob2blob(p_clob IN CLOB,

x_blob IN OUT BLOB,

p_cset_f IN VARCHAR2,

p_cset_t IN VARCHAR2) IS

l_blob_len NUMBER := dbms_lob.getlength(p_clob);

l_buf_cset_t VARCHAR2(32000);

l_cset_id_f NUMBER;

l_cset_id_t NUMBER;

l_dst_offset NUMBER := 1;

l_src_offset NUMBER := 1;

l_warning NUMBER;

l_lang NUMBER := dbms_lob.default_lang_ctx;

BEGIN

l_cset_id_f := heb_char_set_pub.get_cset_id(p_cset_f);

l_cset_id_t := heb_char_set_pub.get_cset_id(p_cset_t);

IF l_cset_id_f IS NULL

OR l_cset_id_t IS NULL THEN

fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,

p_procedure_name => 'blob2clob',

p_error_text => 'Unexpected Error, Character Set is Invalid of p_cset_f:' || p_cset_f || ', p_cset_t:' || p_cset_t);

RAISE fnd_api.g_exc_unexpected_error;

END IF;

dbms_lob.converttoblob(dest_lob => x_blob,

src_clob => p_clob,

amount => dbms_lob.lobmaxsize,

dest_offset => l_dst_offset,

src_offset => l_src_offset,

blob_csid => l_cset_id_t,

lang_context => l_lang,

warning => l_warning);

END clob2blob;

程序二：oracle blob转换为clob(blob2clob)

PROCEDURE blob2clob(p_blob IN BLOB,

x_clob IN OUT CLOB,

p_cset_f IN VARCHAR2,

p_cset_t IN VARCHAR2) IS

l_blob_len NUMBER := dbms_lob.getlength(p_blob);

l_buf_cset_t VARCHAR2(32000);

l_cset_id_f NUMBER;

l_cset_id_t NUMBER;

l_dst_offset NUMBER := 1;

l_src_offset NUMBER := 1;

l_warning NUMBER;

l_lang NUMBER := dbms_lob.default_lang_ctx;

BEGIN

l_cset_id_f := heb_char_set_pub.get_cset_id(p_cset_f);

l_cset_id_t := heb_char_set_pub.get_cset_id(p_cset_t);

IF l_cset_id_f IS NULL

OR l_cset_id_t IS NULL THEN

fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,

p_procedure_name => 'blob2clob',

p_error_text => 'Unexpected Error, Character Set is Invalid of p_cset_f:' || p_cset_f || ', p_cset_t:' || p_cset_t);

RAISE fnd_api.g_exc_unexpected_error;

END IF;

dbms_lob.converttoclob(dest_lob => x_clob,

src_blob => p_blob,

amount => dbms_lob.lobmaxsize,

dest_offset => l_dst_offset,

src_offset => l_src_offset,

blob_csid => l_cset_id_f,

lang_context => l_lang,

warning => l_warning);

END blob2clob;