Moving BLOB data from to Apex on Autonomous Database

Moving BLOB data from 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' ))
    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;
        dbms_lob.createtemporary ( l_clob, true );

            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;


    for c1 in (
        select parameter, value
          from nls_session_parameters
         where parameter in ('NLS_DATE_FORMAT',
        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;

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

        p_table_name => :PX_TABLE, 
        p_xml_content => l_blob );

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

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

        p_table_name => :PX_TABLE, 
        p_xml_content => l_blob );

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



Why are Database consultants so important


One of the most common mistakes that organizations/teams make is not hiring a Database Consultant at the start of the project.

You might think that this is such a silly mistake and people should not do it, however trust me, they don’t and they do it again and again.

At the start of the project most of the times the team just want to hire a UI expert and want to start asap. The UI person creates a webpage and the associated events and later realizes that he/she needs to store the data somewhere (wow!!!) and creates a table and this activity goes on for every page or click the UI person is creating.

This leads to a Database design which is neither scalable, compatible and you can just imagine the performance and latency issues that you will get into.


Database design is the foundation of a well-performing database.

Would you ever consider building a house without a blueprint? The foundation to your Applications, Interfaces and Systems is the database. To ensure that your database will be a good foundation for your applications and programs that rely on it, build it right in the beginning.

What Organizations need to do is to hire a Database consultant at the start of the project and let him design and decide the Data model and the Architecture. If there are budget constraints, at least get .5 FTE from some other project for some time and let him design the Database keeping in mind the environment you are working on. Trust me, this will go a long way for your application. Applications which are designed in such a manner can withstand latency, scalability, availability and compatibility issues in far better manner than others.

The benefits of building it right are many. Just as you would never consider building a house without thoughtful design and planning, so should you approach database design. Imagine the building architect who is designing to your specifications. You’ll be asked questions like “How will you use this?”, “What are your future plans?”, “What do you want this to look like?”, “How do you want it to work?” Asking these same questions when you’re building a database will give you the same usability and “livability” that you’ll get out of a well-designed house.

Properly tuned and deployed Database designs will go a very long way toward ensuring your database environment is functioning at optimal levels. It also ensures that all your stakeholders your Technical architects, DBAs, UI team, Domain managers, users and even business intelligence and applications teams are aligned and working together efficiently.

Happy Designing !!!!