Extracting BLOB from Oracle with Sqlplus

我写过关于将WITH子句函数用于各种目的的文章，为什么我需要内联PL/SQL方法？，其中一个示例是提取包含 XLSX 文件的 BLOB。在那篇文章中，我愉快地挥舞着双手，说使用 Toad 或 SqlDeveloper 将输出提取到文件中。

如果我们想编写这个脚本，该怎么办？有选择。您可以从数据库内部发送电子邮件并附加 blob（请参阅html_email），但如果您的目标是将其自动传送到共享驱动器或其他文件目标，则电子邮件不是一个好的选择。

您可以使用几乎任何支持强大 Oracle 数据库接口的语言从客户端 ETL 服务器执行此操作。Pro-C、Java、Perl DBI::DBD::Oracle 和我认为 Python 都有能力从数据库中提取 BLOB 并写入本地文件系统。我还假设像 Ab-initio 和 Informatica 这样的大型商业 ETL 软件包可以做到这一点，但我还没有看过。

也许您无法访问这些工具，也无法掌握这些工具的专业知识。Oracle客户端库有SQLcl，可以编写脚本来下载BLOB，如本文使用SQLcl将BLOB写入20行js中的文件中提到的。但事实证明，您并不总是在 ETL 服务器上安装 sqlcl 甚至 Java。我知道这看起来很奇怪，但我已经看够了，我不再感到惊讶。

我想使用sqlplus来完成此操作，因为它几乎在每个客户端和每个 ETL 服务器上都可用。不幸的是sqlplus不支持 BLOB 数据类型作为绑定的局部变量。德拉特。当我搜索时，我找到了Ottmar Gobect 撰写的使用 SQLPlus 下载 BLOB 的文章。他对这个想法大加赞扬。解决方案的关键是将sqlplus不支持的 BLOB 转换为支持的 CLOB。你问如何？与我们将二进制文件附加到电子邮件时的方式相同。我们对其进行 Base64 编码。聪明的！

使用 Sqlplus 提取 BLOB

重用顶部提到的文章中的电子表格示例以及 Tim Hall 发布的 base64encode 函数，我们有一个 sqlplus 脚本，该脚本可以生成 BLOB 并将其作为 base64 编码文本提取到文件中。

-- 16mb should be plenty for most spreadsheets.
set long 16777216
set longchunksize 32767
set heading off
set verify off 
set feedback off 
set trimout on 
set trimspool on 
set pagesize 0 
set linesize 1000 
whenever sqlerror exit failure
-- sqlplus supports clob variables but not blob
variable vo_clob clob
--
DECLARE
    c SYS_REFCURSOR;

    FUNCTION base64encode(p_blob IN BLOB)
    RETURN CLOB
    -- -----------------------------------------------------------------------------------
    -- File Name    : https:///dba/miscellaneous/base64encode.sql
    -- Author       : Tim Hall
    -- Description  : Encodes a BLOB into a Base64 CLOB.
    -- Last Modified: 09/11/2011
    -- -----------------------------------------------------------------------------------
    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;

    FUNCTION get_xlsx(p_src SYS_REFCURSOR) 
    RETURN CLOB AS
        v_blob          BLOB;
        v_ctxId         ExcelGen.ctxHandle;
        v_sheetHandle   BINARY_INTEGER;
    BEGIN
        v_ctxId := ExcelGen.createContext();
        v_sheetHandle := ExcelGen.addSheetFromCursor(v_ctxId, 'Employee Salaries', p_src, p_sheetIndex => 1);
        -- freeze the top row with the column headers
        ExcelGen.setHeader(v_ctxId, v_sheetHandle, p_frozen => TRUE);
        -- style with alternating colors on each row. 
        ExcelGen.setTableFormat(v_ctxId, v_sheetHandle, 'TableStyleLight11');
        -- single column format on the salary column. The ID column keeps default format
        ExcelGen.setColumnFormat(
            p_ctxId     => v_ctxId
            ,p_sheetId  => v_sheetHandle
            ,p_columnId => 5        -- the salary column
            ,p_format   => '$#,##0.00'
        );
        v_blob := ExcelGen.getFileContent(v_ctxId);
        ExcelGen.closeContext(v_ctxId);
        RETURN base64encode(v_blob);
    END;
BEGIN

    OPEN c FOR WITH add_bilbo AS (
            SELECT e.employee_id AS employee_id, e.last_name, e.first_name, d.department_name, e.salary
            FROM hr.employees e
            INNER JOIN hr.departments d
                ON d.department_id = e.department_id
            UNION ALL
            SELECT 999 AS employee_id, 'Baggins' As last_name, 'Bilbo' as first_name, 'Sales' AS department_name
                ,123.45 AS salary
            FROM dual
        ) SELECT * FROM add_bilbo ORDER BY last_name, first_name
    ;
    -- assign the uuencoded clob data to the sqlplus bind variable
    :vo_clob := get_xlsx(c);
END;
/
-- spool out the encoded clob
set termout off
spool "x.xlsx.base64"
print vo_clob
spool off
set termout on
--  base64 -d -i x.xlsx.base64 >x.xlsx && rm x.xlsx.base64
-- OR on windows
-- certutil -decode x.xlsx.base64 x.xlsx
-- del x.xlsx.base64

在我的 Windows 机器上，我可以在 cygwin 中运行以下命令。我认为它在 Linux 中工作得很好。

base64 -d -i spool_file_name > spreadsheet_name.xlsx

如果您未能包含 -i 选项，它可能会抱怨输入无效。事实证明，我的 cygwin 版本不能容忍 sqlplus 放入文件中的回车符 (\015)。换行符（\012）没问题。-i 选项使其忽略它们。在真正的 Unix 机器上可能没有必要，或者如果 Windows 上的 sqlplus 中有关闭回车的选项。我没有试图找出答案。

在 Windows 上，以下命令运行良好。

certutil -decode spool_file_name spreadsheet_name.xlsx

在我的小示例中，编码使文件大小增加了大约 25%。我并不是说这就是您将获得的金额，但应该在这个范围内。这还不足以让你倾家荡产。

结论

如果您希望从 Oracle 中获取 BLOB，并且无法使用更好的脚本语言或工具，那么知道至少可以使用 sqlplus 来实现这一点是一个不错的选择。