Export Application

Helper: CLOB → BLOB (UTF-8)

create or replace function clob_to_blob_utf8(p_clob in clob) return blob
is
  l_blob         blob;
  l_dest_offset  integer := 1;
  l_src_offset   integer := 1;
  l_lang_ctx     integer := dbms_lob.default_lang_ctx;
  l_warning      integer;
begin
  dbms_lob.createtemporary(l_blob, true, dbms_lob.call);
  dbms_lob.converttoblob(
    dest_lob     => l_blob,
    src_clob     => p_clob,
    amount       => dbms_lob.lobmaxsize,
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    blob_csid    => nls_charset_id('AL32UTF8'),
    lang_context => l_lang_ctx,
    warning      => l_warning
  );
  return l_blob;
end;
/

1) Export an app to a ZIP and store it in a table

-- 1) One-time table to keep your exports
create table app_exports (
  id         number generated always as identity primary key,
  file_name  varchar2(255) not null,
  content    blob           not null,
  created_on date           default sysdate
);


-- 2) Export APP 100, split files and zip them into one BLOB, then save it
declare
  l_files apex_t_export_files;
  l_zip   blob;
begin
  -- Get all app files (pages, shared comps, etc.)
  l_files := apex_export.get_application(
               p_application_id => 100,
               p_split          => true);  -- true = many files (recommended)
  dbms_lob.createtemporary(l_zip, true);
  -- Zip all returned files
  for i in 1 .. l_files.count loop
    apex_zip.add_file(
      p_zipped_blob => l_zip,
      p_file_name   => l_files(i).name,
      p_content     => l_files(i).contents );  -- contents is BLOB here
  end loop;
  apex_zip.finish(l_zip);
  insert into app_exports (file_name, content)
  values ('f100_export_'||to_char(sysdate,'yyyymmdd_hh24miss')||'.zip', clob_to_blob_utf8(l_zip) );
  dbms_lob.freetemporary(l_zip);
end;
/

2) Export and download as a file from an APEX page (On-Demand process)

Create an On-Demand PL/SQL process and paste this block. Call it from a button/link via apex.server.process(...).

declare
  l_files apex_t_export_files;
  l_zip   blob;
  l_name  varchar2(200) := 'app_'|| :APP_ID ||'_'||to_char(sysdate,'yyyymmdd_hh24miss')||'.zip';
begin
  l_files := apex_export.get_application(
               p_application_id => :APP_ID,
               p_split          => true);
  dbms_lob.createtemporary(l_zip, true);
  for i in 1 .. l_files.count loop
    apex_zip.add_file(l_zip, l_files(i).name, l_files(i).contents );
  end loop;
  apex_zip.finish(l_zip);
  -- Emit as a download
  owa_util.mime_header('application/zip', false);
  htp.p('Content-Length: ' || dbms_lob.getlength(l_zip));
  htp.p('Content-Disposition: attachment; filename="'||l_name||'"');
  owa_util.http_header_close;
  wpg_docload.download_file(l_zip);
  apex_application.stop_apex_engine; -- end request cleanly
end;

Client-side call example (Dynamic Action → Execute JavaScript):

apex.server.process("YOUR_ON_DEMAND_PROCESS_NAME", { }, { dataType: "text" });

3) Export a single SQL file (no ZIP) and store it

If you prefer one .sql file containing the whole app:

declare
  l_files apex_t_export_files;
  l_name  varchar2(4000);
  l_sql   clob;  -- single .sql export text
  l_zip   blob;  -- optional: zip the single file after conversion
  l_blob  blob;  -- converted from CLOB
begin
  -- Single-file export (monolithic .sql)
  l_files := apex_export.get_application(
               p_application_id          => :APP_ID,
               p_split                   => false,
               p_with_ir_private_reports => true
             );

  l_name := l_files(1).name;         -- e.g., f123.sql

  l_sql  := to_clob(l_files(1).contents); 
  
  -- Convert CLOB -> BLOB (UTF-8)
  l_blob := clob_to_blob_utf8(l_sql);

  -- Optional: if you want the monolithic .sql inside a zip, wrap it:
  dbms_lob.createtemporary(l_zip, true);
  apex_zip.add_file(l_zip, l_name, l_blob);  -- add our single .sql as BLOB
  apex_zip.finish(l_zip);

  insert into app_exports (file_name, content)
  values ('f'||:APP_ID||'_single_sql_'||to_char(sysdate,'yyyymmdd_hh24miss')||'.zip', l_zip);

  dbms_lob.freetemporary(l_zip);
end;
/

4) Export multiple apps (e.g., whole workspace)

declare
  l_zip   blob;
  l_files apex_t_export_files;
begin
  dbms_lob.createtemporary(l_zip, true);
  for r in (select application_id from apex_applications order by application_id) loop
    l_files := apex_export.get_application(p_application_id => r.application_id, p_split => true);
    for i in 1 .. l_files.count loop
      apex_zip.add_file(
        p_zipped_blob => l_zip,
        p_file_name   => 'f'||r.application_id||'/'||l_files(i).name,
        p_content     =>  l_files(i).contents ) ;
    end loop;
  end loop;
  apex_zip.finish(l_zip);
  insert into app_exports (file_name, content)
  values ('all_apps_'||to_char(sysdate,'yyyymmdd_hh24miss')||'.zip', clob_to_blob_utf8(l_zip) );
  dbms_lob.freetemporary(l_zip);
end;
/

Notes & tips
    •    APEX_EXPORT.GET_APPLICATION returns a collection (APEX_T_EXPORT_FILES) where 
          each element has NAME, CONTENTS (BLOB), and MIME_TYPE.
    •    Use p_split => true for a clean, multi-file export (pages, shared components, etc.). Set to false for one big .sql.
    •    You can add flags like p_with_translations => true and p_with_supporting_objects => true if your app uses them.
    •    To write to the server filesystem instead of a table, wrap the final BLOB with UTL_FILE (convert to CLOB if needed) 
          or keep using ZIP+BLOB and push with your own file mover.

List private saved reports (for reference)

-- Interactive Report (legacy IR)
select application_id, page_id, region_id, report_id, report_name, report_type, owner
from apex_application_page_ir_rpt
where report_type = 'PRIVATE'
order by application_id, page_id, report_name;

-- Interactive Grid
select application_id, page_id, region_id, report_id, name as report_name, visibility, created_by
from apex_appl_page_ig_rpts
where visibility = 'Private'
order by application_id, page_id, report_name;

Example including private IR reports and put the result into a ZIP BLOB table

declare
  l_files apex_t_export_files;
  l_zip   blob;
begin
  -- Export app 100 including private Interactive Report reports
  l_files := apex_export.get_application(
               p_application_id          => 100,
               p_split                   => true,
               p_with_ir_private_reports => true   -- <<< include private reports
             );
  dbms_lob.createtemporary(l_zip, true);
  for i in 1 .. l_files.count loop
    apex_zip.add_file(
      p_zipped_blob => l_zip,
      p_file_name   => l_files(i).name,
      p_content     => l_files(i).contents 
    );
  end loop;
  apex_zip.finish(l_zip);
  insert into app_exports (file_name, content)
  values ('f100_with_priv_ir_'||to_char(sysdate,'yyyymmdd_hh24miss')||'.zip', clob_to_blob_utf8(l_zip) );
  dbms_lob.freetemporary(l_zip);
end;
/

Notes
    •    p_with_ir_private_reports => true tells APEX to include each developer/user’s private saved reports.
    •    Those reports will be exported into separate SQL files under the app export folder 
          (for example:  f100/application/pages/page_10/ir_private_reports.sql).
    •    Be aware: importing such an export into another workspace will create those private reports for 
          their original owners (by username).

Parameter details

p_application_id

ID of the APEX application to export (mandatory).

p_split
    •    TRUE → export broken into multiple files (per page, per component).
    •    FALSE → single monolithic .sql file.

p_with_ir_public_reports

Includes public saved reports for Interactive Reports and Grids.
(default = TRUE)

p_with_ir_private_reports

Includes private saved reports owned by individual users.
(default = FALSE)

p_with_ir_notifications

Includes Interactive Report subscriptions (e-mail notifications).
(default = FALSE)

p_with_translations

Include translated applications (if you’ve generated translations).
(default = FALSE)

p_with_supporting_objects

Include Supporting Objects (install scripts, seed data, etc.).
(default = FALSE)

p_with_acl_assignments

Include Access Control List assignments.
(default = FALSE)

p_with_original_ids

Keep original IDs for application components.
Useful for migration between environments.
(default = TRUE)

p_with_export_as_zip

    •    If TRUE, the function returns a single file entry containing a ZIP.
    •    If FALSE, you get an array of files, which you can ZIP yourself.
(default = FALSE)