Oracle调用SOAP服务返回PDF转BLOB存储后文件无效无法打开

问题场景 #

调用基于SOAP协议的Web服务时，服务响应会返回PDF文件内容，需要将响应中的PDF解析为BLOB类型做持久化存储。现有PL/SQL调用代码可成功获取服务返回的PDF相关内容，但最终生成的PDF文件无效，无法正常打开、下载。

原实现代码如下：

declare
p_ref_no varchar2(100) := '290313008810';
l_key varchar2(100) := post_shipments_shipper.getKey('LIVE');
l_envelope  CLOB;
l_xml       XMLTYPE;
l_result    varchar2(2000);
l_pdf       clob;
l_pdf_file blob;
pragma autonomous_transaction;
    begin
        l_envelope := '<?xml version="1.0" encoding="UTF-8"?>
         <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sms="http://track.smsaexpress.com/secom/SMSAWebserviceIntl">
            <soapenv:Body>
                <sms:getPDF>
                 <sms:awbNo>'||p_ref_no||'</sms:awbNo>
                 <sms:passKey>'||l_key||'</sms:passKey>                            
              </sms:getPDF>
           </soapenv:Body>
        </soapenv:Envelope>';
      -- 发起Web Service请求获取XML响应
      l_xml := APEX_WEB_SERVICE.make_request(
        p_url      => 'http://track.smsaexpress.com/SECOM/SMSAwebServiceIntl.asmx',
        p_action   => 'http://track.smsaexpress.com/secom/SMSAWebserviceIntl/getPDF',
        p_envelope => l_envelope
      );
     
      l_pdf := APEX_WEB_SERVICE.parse_xml_clob(
        p_xml   => l_xml,
        p_xpath => '//getPDFResult',
        p_ns    => 'xmlns="http://track.smsaexpress.com/secom/SMSAWebserviceIntl"'
      );    
              
        l_pdf_file := APEX_WEB_SERVICE.CLOBBASE642BLOB(l_pdf);

        delete from shipment_labels;

        insert into shipment_labels values (l_pdf_file,'abc.pdf','application/pdf',l_pdf);
        commit;          

    end;

故障原因 #

存储后的PDF无法打开，核心是Base64解码环节得到的二进制内容损坏，常见诱因有3个：

• 从XML节点提取的Base64字符串夹带换行、空格、缩进等多余空白字符，部分场景还会带XML转义字符、data:application/pdf;base64,这类前缀，直接解码会导致二进制内容错乱
• 发起SOAP请求时没有显式指定字符集，数据库和服务端字符集不匹配时会转义损坏Base64中的特殊字符
• 直接用parse_xml_clob提取长Base64内容时，若内容超过CLOB默认读写缓冲区长度，会出现截断

修复方案 #

按以下逻辑调整代码即可：

1. 发起请求时显式指定UTF-8字符集
2. 提取到Base64字符串后先做清洗：移除所有空白字符、换行、回车，剔除可能存在的Data URI前缀
3. 解码后增加简单校验，确认二进制内容开头为PDF固定标识%PDF-

修复后的可运行代码：

declare
  p_ref_no    varchar2(100) := '290313008810';
  l_key       varchar2(100) := post_shipments_shipper.getKey('LIVE');
  l_envelope  CLOB;
  l_xml       XMLTYPE;
  l_pdf       clob;
  l_pdf_file  blob;
  l_pdf_header raw(5);
  pragma autonomous_transaction;
begin
  l_envelope := '<?xml version="1.0" encoding="UTF-8"?>
     <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sms="http://track.smsaexpress.com/secom/SMSAWebserviceIntl">
        <soapenv:Body>
            <sms:getPDF>
             <sms:awbNo>'||p_ref_no||'</sms:awbNo>
             <sms:passKey>'||l_key||'</sms:passKey>                            
          </sms:getPDF>
       </soapenv:Body>
    </soapenv:Envelope>';

  -- 显式指定字符集发起请求
  l_xml := APEX_WEB_SERVICE.make_request(
    p_url      => 'http://track.smsaexpress.com/SECOM/SMSAwebServiceIntl.asmx',
    p_action   => 'http://track.smsaexpress.com/secom/SMSAWebserviceIntl/getPDF',
    p_envelope => l_envelope,
    p_charset  => 'AL32UTF8'
  );

  l_pdf := APEX_WEB_SERVICE.parse_xml_clob(
    p_xml   => l_xml,
    p_xpath => '//getPDFResult',
    p_ns    => 'xmlns="http://track.smsaexpress.com/secom/SMSAWebserviceIntl"'
  );    

  -- 清洗Base64内容：移除所有空白、换行，剔除可能存在的前缀
  l_pdf := replace(replace(replace(l_pdf, chr(10), ''), chr(13), ''), ' ', '');
  l_pdf := trim(l_pdf);
  if instr(l_pdf, ',') > 0 then
    l_pdf := substr(l_pdf, instr(l_pdf, ',') + 1);
  end if;

  -- Base64转BLOB
  l_pdf_file := APEX_WEB_SERVICE.CLOBBASE642BLOB(l_pdf);

  -- 校验PDF头，确认解码正确
  dbms_lob.read(l_pdf_file, 5, 1, l_pdf_header);
  if utl_raw.cast_to_varchar2(l_pdf_header) != '%PDF-' then
    raise_application_error(-20001, 'PDF内容解码损坏，请检查Base64字符串');
  end if;

  delete from shipment_labels;
  insert into shipment_labels values (l_pdf_file,'abc.pdf','application/pdf',l_pdf);
  commit;          
end;
/

内容的提问来源于stack exchange，提问作者Adeel Aslam