-- 当前数据库可识别的字符集 NLS_CHARACTERSET
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM NLS_INSTANCE_PARAMETERS;
SELECT USERENV('LANGUAGE') FROM DUAL;
SELECT * FROM NLS_SESSION_PARAMETERS;
-- 查看表的字符集
SELECT DISTINCT(USERENV('LANGUAGE')) FROM table;
BOLB转字符串小于4000
-- blob长度小于2000
SELECT
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(HEAD_IMG))
FROM
table
WHERE
HEAD_IMG IS NOT NULL;
-- blob长度大于4000
SELECT
UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(DBMS_LOB.SUBSTR(HEAD_IMG, 2000, 1),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8')) COLUME_NAME1,
UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(DBMS_LOB.SUBSTR(HEAD_IMG, 2000, 2001),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8')) COLUME_NAME2,
UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(DBMS_LOB.SUBSTR(HEAD_IMG, 2000, 4001),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8')) COLUME_NAME3
FROM
table
WHERE
HEAD_IMG IS NOT NULL;
-- blob长度大于2000,小于4000
SELECT
UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(DBMS_LOB.SUBSTR(HEAD_IMG, 2000, 1),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'))
|| UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CONVERT(DBMS_LOB.SUBSTR(HEAD_IMG, 2000, 2001),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'))
FROM
table
WHERE
HEAD_IMG IS NOT NULL;
SELECT CONVERT(UTL_RAW.CAST_TO_VARCHAR2(HEAD_IMG), 'ZHS16GBK', 'AL32UTF8') FROM table WHERE HEAD_IMG IS NOT NULL;
BOLB转字符串大于4000
--Only for Oracle 注意字段数据类型转换
Create Or Replace Function Blob_To_Varchar (Blob_In In Blob) Return Varchar2
Is
V_Varchar Varchar2(4000);
V_Start Pls_Integer := 1;
V_Buffer Pls_Integer := 2000;
Begin
If Dbms_Lob.Getlength(Blob_In) Is Null Then
Return '';
End If;
For I In 1..Ceil(Dbms_Lob.Getlength(Blob_In) / V_Buffer) Loop
-- 当转换出来的字符串乱码时: 'AMERICAN_AMERICA.ZHS16GBK' 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'
-- Convert 可以忽略当前2进制数据存储使用的字符集(第三个参数) 'ZHS16GBK' 'AL32UTF8' 'ZHS32GB18030' 'ZHS16CGB231280'
-- V_Varchar := Utl_Raw.Cast_To_Varchar2(UTL_RAW.CONVERT(Dbms_Lob.Substr(Blob_In, V_Buffer), 'AL32UTF8', 'AMERICAN_AMERICA.ZHS16GBK'));
V_Varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start));
V_Start := V_Start + V_Buffer;
End Loop;
Return V_Varchar;
End Blob_To_Varchar;
SELECT Blob_To_Varchar(HEAD_IMG) FROM table WHERE HEAD_IMG IS NOT NULL;
blob转base64
CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB) RETURN CLOB
IS
l_clob CLOB;
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP
l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, l_step, i * l_step + 1)));
END LOOP;
RETURN l_clob;
END;
SELECT HEAD_IMG,base64encode(HEAD_IMG) FROM table WHERE HEAD_IMG IS NOT NULL;
varchar转Blob
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 b_blob from dual;
end if;
RETURN b_blob;
END V2B;