如何在SQR中访问PeopleSoft页面附件并使用其数据？

问题背景 #

需要编写SQR读取PeopleSoft页面附件数据，尝试两种SQL方法后遇到问题：

1. 基础SQL查询能返回数据，但不知如何转为可正常打开的文件格式

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,1000,1))
FROM psfile_attdet
WHERE ATTACHSYSFILENAME = $sysfilename

2. 带解压的SQL执行报错

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(UTL_COMPRESS.LZ_UNCOMPRESS(file_data),2000,1))
FROM psfile_attdet
WHERE ATTACHSYSFILENAME = $sysfilename

错误信息：

(SQR 5528) ORACLE OCIStmtExecute error 29294 in cursor 2:  

ORA-29294: A data error occurred during compression or uncompression. 

ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 60 

ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 230 

ORA-06512: at "SYS.UTL_COMPRESS", line 89

同时尝试了一段SQR代码，但不确定正确性：

Begin-Procedure download-file

Begin-Select
!utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,1000,1)) &file

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(UTL_COMPRESS.LZ_UNCOMPRESS(file_data),2000,1)) &file

do get-file-invoices
let $PSfile = &file

FROM psfile_attdet 
WHERE ATTACHSYSFILENAME = $sysfilename

End-Select

End-procedure !download-file

解决方案 #

1. 先确认附件压缩状态 #

PeopleSoft附件并非全部经过压缩，ORA-29294错误说明你尝试解压的文件未被压缩，或压缩格式与UTL_COMPRESS.LZ_UNCOMPRESS不匹配。先查询压缩标识：

SELECT FILE_COMPRESSION 
FROM psfile_attdet
WHERE ATTACHSYSFILENAME = $sysfilename

• 返回Y：文件已压缩，需执行解压步骤
• 返回N：直接读取即可，无需解压

2. 完整读取LOB并写入文件 #

SQR处理附件时不能仅截取部分内容，需完整读取LOB并分块写入本地文件，以下是两种场景的完整代码：

场景1：文件未压缩

Begin-Procedure extract-attachment
  Let $sysfilename = 'your_attachment_filename'  ! 替换为实际附件系统文件名
  Let $output_path = 'C:\local_output\target_file.ext'  ! 替换为本地输出路径+正确扩展名

  Begin-Select
    file_data &file_lob
    FROM psfile_attdet
    WHERE ATTACHSYSFILENAME = $sysfilename
    Do write-lob-to-file(&file_lob, $output_path)
  End-Select
End-Procedure

Begin-Procedure write-lob-to-file(&lob_in, $output_path)
  Open $output_path For Binary-Write
  If #status != 0
    Display '无法打开输出文件: ' $output_path
    Stop
  End-If

  ! 分块读取LOB避免内存溢出
  Let #offset = 1
  Let #chunk_size = 32767  ! 每次读取32KB，可根据文件大小调整
  Loop
    Select dbms_lob.substr(&lob_in, #chunk_size, #offset)
      Into &chunk_data
      From dual
    Let #bytes_read = length(&chunk_data)
    If #bytes_read = 0
      Break
    End-If
    Write $output_path From &chunk_data Binary
    Let #offset = #offset + #chunk_size
  End-Loop

  Close $output_path
End-Procedure

场景2：文件已压缩

若查询到FILE_COMPRESSION = 'Y'，修改读取逻辑为解压后再写入：

Begin-Select
  utl_compress.lz_uncompress(file_data) &uncompressed_lob
  FROM psfile_attdet
  WHERE ATTACHSYSFILENAME = $sysfilename
  Do write-lob-to-file(&uncompressed_lob, $output_path)
End-Select

3. 关键注意事项 #

• 确保SQR运行用户拥有PSFILE_ATTDET表的读取权限，以及本地输出目录的写入权限
• 输出文件名必须使用原附件的正确扩展名（如.pdf、.xls），否则文件无法正常打开
• 超大文件（>1GB）可适当调整#chunk_size，但不要超过SQR变量长度限制

内容的提问来源于stack exchange，提问作者Jason Rupp