能否使用Apex Data Export将导出文件存储为表中BLOB？

Yes, you absolutely can use Apex Data Export to generate files (like PDF) and store them as BLOBs in your database table. The error you're seeing (PLS-00382: expression is of wrong type) comes from a type mismatch between the variable you're using and what the UPDATE statement expects.

Root Cause #

You declared l_export as apex_data_export.t_export, but the file_blob column is a BLOB type. The apex_data_export.export function returns a t_export object by default, not a raw BLOB. To get a BLOB directly, you need to use the correct overload of the export function that specifies the output type as BLOB.

Corrected Code #

Here's the fixed version of your code that properly generates a BLOB and stores it in your table:

DECLARE
    l_context apex_exec.t_context; 
    l_export  BLOB; -- Changed type to BLOB
BEGIN
    -- Open query context
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select * from emp' );

    -- Export query result as PDF BLOB
    l_export := apex_data_export.export (
        p_context   => l_context,
        p_format    => apex_data_export.c_format_pdf,
        p_output    => apex_data_export.c_output_blob -- Specify output as BLOB
    );

    -- Close the context (always do this after use)
    apex_exec.close( l_context );

    -- Update the table with the BLOB
    UPDATE my_table
    SET file_blob = l_export;
    
    -- Commit if needed (depends on your transaction context)
    -- COMMIT;

EXCEPTION
    WHEN others THEN
        -- Ensure context is closed even on error
        IF apex_exec.is_open(l_context) THEN
            apex_exec.close( l_context );
        END IF;
        raise;
END;
/

Key Changes: #

• Changed l_export type from apex_data_export.t_export to BLOB
• Added p_output => apex_data_export.c_output_blob parameter to apex_data_export.export to explicitly request a BLOB output
• Added a check IF apex_exec.is_open(l_context) in the exception block to avoid trying to close an already closed context

Additional Notes: #

• If you need to store other metadata (like filename, MIME type), you can retrieve those by using the t_export object approach (access properties like filename or mime_type), but for direct BLOB storage, the above method is simpler.
• Remember to commit the transaction if your environment doesn't auto-commit (e.g., in a PL/SQL block run via SQL Developer or Apex SQL Workshop).

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