Export CLOB as a SQL Script #JoelKallmanDay

Quite often I will need to export some data from one system, such as system setup metadata, preferences, etc. that need to be included in a repository and imported when the application is installed elsewhere.

I might export the data in JSON or CSV or some other text format as a CLOB (character large object) variable. I then need to wrap this in suitable commands so that it will execute as a SQL script when installed in the target system. To do this I use a simple script that takes advantage of the APEX_STRING API to split the CLOB into chunks and generate a SQL script that will re-assemble those chunks back into a CLOB on the target database, then call a procedure that will process the data (e.g. it might parse the JSON and insert metadata into the target tables).

This will work even if the incoming CLOB has lines that exceed 32K in length, e.g. a JSON document that includes embedded image data encoded in base 64, or documents with multibyte characters.

This is clob_to_sql_script:

function clob_to_sql_script (
    p_clob           in varchar2,
    p_procedure_name in varchar2,
    p_chunk_size     in integer := 8191
) return clob is

-- Takes a CLOB, returns a SQL script that will call the given procedure
-- with that clob as its parameter.

    l_strings apex_t_varchar2;
    l_chunk   varchar2(32767);
    l_offset  integer;        

begin

    apex_string.push(
        l_strings,
        q'[
declare
l_strings apex_t_varchar2;
procedure p (p_string in varchar2) is
begin
    apex_string.push(l_strings, p_string);
end p;
begin
]');
    
    while apex_string.next_chunk (
        p_str    => p_clob,
        p_chunk  => l_chunk,
        p_offset => l_offset,
        p_amount => p_chunk_size )
    loop
        apex_string.push(
            l_strings,
            q'[p(q'~]'
            || l_chunk
            || q'[~');]');
    end loop;    

    apex_string.push(
        l_strings,
        replace(q'[
    #PROC#(apex_string.join_clob(l_strings));
end;
]',
            '#PROC#', p_procedure_name)
        || '/');

    return apex_string.join_clob(l_strings);
end clob_to_sql_script;

Note that the default chunk size is 8,191 characters which is the safe limit for multi-byte characters. You can choose a smaller chunk size if you want, although if the incoming CLOB is very large, the smaller the chunk size the bigger the expanded SQL script will be.

A simple test case will demonstrate what it will do:

declare
    l_input  clob;
    l_output clob;
begin
    l_input := q'[
{
    "data": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
}
]';
    l_output := clob_to_sql_script(
        p_clob           => l_input,
        p_procedure_name => 'mypackage.import',
        p_chunk_size     => 60 );
    dbms_output.put_line( l_output );
end;
/

The above script would output this:

declare
l_strings apex_t_varchar2;
procedure p (p_string in varchar2) is
begin
    apex_string.push(l_strings, p_string);
end p;
begin

p(q'~
{
    "data": "Lorem ipsum dolor sit amet, consectetur adip~');
p(q'~iscing elit, sed do eiusmod tempor incididunt ut labore et d~');
p(q'~olore magna aliqua. Ut enim ad minim veniam, quis nostrud ex~');
p(q'~ercitation ullamco laboris nisi ut aliquip ex ea commodo con~');
p(q'~sequat. Duis aute irure dolor in reprehenderit in voluptate ~');
p(q'~velit esse cillum dolore eu fugiat nulla pariatur. Excepteur~');
p(q'~ sint occaecat cupidatat non proident, sunt in culpa qui off~');
p(q'~icia deserunt mollit anim id est laborum."
}
~');

    mypackage.import(apex_string.join_clob(l_strings));
end;
/

The source can be downloaded from here: clob_to_sql_script.sql

Jeffrey Kemp 11 October 2022

APEX / apex-22.1 / PL/SQL / tips-&-tricks /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.