Oracle Apex/PLSQL: Can i use apex data export to store the file into another table as a blob?

Question

I'm trying to figure out how to store the result of an sql query into a blob column. The current setup: I am trying to build a "File store" of sorts. Users can upload files, I will then go through those files, edit them and then give them an option to download those edited files. I'm using Oracle Apex to do this. So the uploaded files are stored as a blob in the database table, this is done quite easily with Apex. I would like to edit this file, and store it again as a blob in the table. Basically, I just need to reverse the upload functionality but it doesn't seem to be quite straightforward.

The code roughly likes this:

DECLARE
    l_context apex_exec.t_context; 
    l_export  apex_data_export.t_export;
BEGIN
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select * from emp' );

    l_export := apex_data_export.export (
                        p_context   => l_context,
                        p_format    => apex_data_export.c_format_pdf );

    apex_exec.close( l_context );

    UPDATE my_table
    SET file_blob = l_export;

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;

This gives me an error 'PLS-00382: expression is of wrong type'. However the documentation mentions that the export function exports a file as a blob.

Is this possible to be achieved with Apex Data Export?

Answer 1

In your code, l_export is of datatype apex_data_export.t_export. In the docs you can see what that datatype is exactly. Record type apex_data_export.t_export has a column content_blob - maybe you could do

 UPDATE my_table
    SET file_blob = l_export.content_clob;

I have not done any testing but this is where I'd start.