Oracle BLOB转字符串

-- 当前数据库可识别的字符集 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', 'AMER