Oracle BLOB转字符串

本文介绍了如何在Oracle数据库中将BLOB类型的数据转换为字符串,特别是当数据长度小于4000和大于4000字符的情况,并详细讲解了BLOB到Base64的转换过程。对于超过4000字符的字符串,提供了解决方案以确保完整转换。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

-- 当前数据库可识别的字符集 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;
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值