Moving BLOB data from apex.oraclecorp.com to Apex on Autonomous Database

The below steps would work for less data may  be 1-2Gb.

Step 1: Go to Data Workshop as show below

Step 2: Once inside Data workshop, you XML to unload the data to your desktop.

Step 3: if you want you can zip the file if the file size is huge. In these cases it will zip to 60-80%, which is really good.

Step 4: Go to the new Apex where you want to upload the file.

Step 5: Under your workspace, choose “Shared Components”

Step 6: Goto “Static Application Files”

Step 7: Upload your zip file here

Step 8: Once uploaded you will be able to see your file in

select * from apex_application_files

this will confirm that the file has been uploaded in the Apex folder.

Step 9: create procedure as shown below

    create or replace procedure load_xml_to_existing_table(
    p_xml_content blob,
    p_table_name  varchar2,
    p_table_owner varchar2 default sys_context('userenv','current_user' ))
is
    l_clob                    clob;

    l_ins_ctx                 dbms_xmlstore.ctxType;
    l_rows                    number;
    l_date_format             varchar2(255); 

    l_nls_date_format         varchar2(255);
    l_nls_timestamp_format    varchar2(255);
    l_nls_timestamp_tz_format varchar2(255);

    c_nls_date_format         varchar2(255) := 'rrrr-MM-dd"T"HH24:mi:ss."000"';
    c_nls_timestamp_format    varchar2(255) := 'rrrr-MM-dd"T"HH24:mi:ss.FF3';
    c_nls_timestamp_tz_format varchar2(255) := 'rrrr-MM-dd"T"HH24:mi:ss.FF3 TZR';

    procedure convert_to_clob is
        l_dest_offset  number := 1;
        l_src_offset   number := 1;
        l_lang_context number := dbms_lob.default_lang_ctx;
        l_warning      number;
    begin
        dbms_lob.createtemporary ( l_clob, true );

        dbms_lob.converttoclob(
            dest_lob      => l_clob,
            src_blob      => p_xml_content,
            amount        => dbms_lob.lobmaxsize,
            dest_offset   => l_dest_offset,
            src_offset    => l_src_offset,
            blob_csid     => nls_charset_id('AL32UTF8'),
            lang_context  => l_lang_context,
            warning       => l_warning );

    end convert_to_clob;

begin
    convert_to_clob;    

    for c1 in (
        select parameter, value
          from nls_session_parameters
         where parameter in ('NLS_DATE_FORMAT',
                             'NLS_TIMESTAMP_FORMAT',
                             'NLS_TIMESTAMP_TZ_FORMAT')) 
    loop
        if c1.parameter = 'NLS_DATE_FORMAT'            then 
            l_nls_date_format         := c1.value;
        elsif c1.parameter = 'NLS_TIMESTAMP_FORMAT'    then 
            l_nls_timestamp_format    := c1.value;
        elsif c1.parameter = 'NLS_TIMESTAMP_TZ_FORMAT' then 
            l_nls_timestamp_tz_format := c1.value;
        end if;
    end loop;

    dbms_session.set_nls( 'NLS_DATE_FORMAT',         
                          '''' || c_nls_date_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_FORMAT',    
                          '''' || c_nls_timestamp_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_TZ_FORMAT', 
                          '''' || c_nls_timestamp_tz_format || '''');

    l_ins_ctx := dbms_xmlstore.newContext( 
                                   dbms_assert.enquote_name( p_table_owner ) || '.' || 
                                   dbms_assert.enquote_name( p_table_name ) );

    dbms_xmlstore.setrowtag( l_ins_ctx, 'ROW' );
    l_rows := dbms_xmlstore.insertxml( l_ins_ctx, l_clob );
    dbms_xmlstore.closecontext( l_ins_ctx );

    dbms_session.set_nls( 'NLS_DATE_FORMAT',         
                          '''' || l_nls_date_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_FORMAT',    
                          '''' || l_nls_timestamp_format || '''');
    dbms_session.set_nls( 'NLS_TIMESTAMP_TZ_FORMAT', 
                          '''' || l_nls_timestamp_tz_format || '''');

    if dbms_lob.istemporary( l_clob ) = 1 then
        dbms_lob.freetemporary( l_clob );
    end if;
end load_xml_to_existing_table;

declare
    l_blob blob;
begin
    select blob_content
      into l_blob
      from apex_application_files
     where name = :PX_XML_FILE;

    load_xml_to_existing_table( 
        p_table_name => :PX_TABLE, 
        p_xml_content => l_blob );
end;

Step 10: Initiate the procedure using the below code. Pass your file name and the table in which you want to insert the data.

declare
    l_blob blob;
begin
    select blob_content
      into l_blob
      from apex_application_files
     where name = :PX_XML_FILE;

    load_xml_to_existing_table( 
        p_table_name => :PX_TABLE, 
        p_xml_content => l_blob );
end;

Step 11: Query the table to see if the load has completed. Finish.

reference: https://blogs.oracle.com/apex/post/load-data-into-existing-tables-with-apex-191