For versioning reasons, in my case Subversion, I regularly have to create the .sql export files of the following APEX application components:

  • the APEX application itself
  • the pages that belong to the application
  • the workspace in which the application is deployed

As you probably know, APEX offers these three capabilities built in. The default export functionality works perfectly for the application and workspace export. The sole problem I encountered was when I tried to find an efficient way to export each page within my application to a separate .sql file.

My first thought went to using the component export functionality in which I can select all pages and add them to the export. Unfortunately, the result of this export is one big .sql file… no good for Subversion.

Another option I read about was using the Java APEXExport or APEXExportSplitter tool which can be found in the APEX installation folder. I never tried out this option but it seems that it does not offer the functionality I am looking for (correct me if I am wrong).

My last resort was to use the export functionality on page definition level. A time-consuming and impractical solution, especially when your application counts more than, let’s say, a hundred pages.

That’s why I came up with something completely else. I figured out that the wwv_flow_utilities package includes the following three functions:

  • export_application_to_clob
  • export_page_to_clob
  • export_workspace_to_clob

P.S. Packages starting with wwv are internal packages and are subject to change in future versions of APEX. I am currently using version 4.1.1.

The above three functions return a CLOB. This took me thinking since it’s pretty easy to write the content of a CLOB to a file in an Oracle directory. Simply use the procedure below to do so.

procedure pro_clob_to_file(p_clob      in clob
                         , p_file_name in varchar2
                         , p_ora_dir   in varchar2) as
  l_clob_length number;
  l_start number := 1;
  byte_length number := 32767;
  l_buffer varchar2(32767);
  l_output utl_file.file_type;
begin
  l_clob_length := dbms_lob.getlength(p_clob);

  l_output := utl_file.fopen(upper(p_ora_dir)
                           , p_file_name
                           , 'wb'
                           , 32767);

  while l_start < l_clob_length loop
    dbms_lob.read(p_clob, byte_length, l_start, l_buffer);
    utl_file.put_raw(l_output, utl_raw.cast_to_raw(l_buffer), true);
    l_start := l_start + byte_length;
  end loop;
utl_file.fclose(l_output);
exception
  when others then
    if utl_file.is_open(l_output) then
      utl_file.fclose(l_output);
    end if;
    raise;
end pro_clob_to_file;

Update on 21-08-2013: or simply use dbms_xslprocessor.clob2file.

Next, I examined the parameters of the to_clob export functions and wrapped them into custom procedures. Each procedure writes a single APEX application, page or workspace export file to an Oracle directory. I parameterized the procedures so you can call the functions according to your own requirements.

Application export

procedure pro_export_app(p_app_id          in number
                       , p_exp_ir_pub_rep  in varchar2
                       , p_exp_ir_priv_rep in varchar2
                       , p_exp_ir_notif    in varchar2
                       , p_ora_dir         in varchar2) as
  l_clob clob;
  l_file_name varchar2(255);
begin
  -- example: app117_export.sql
  l_file_name := 'app' || p_app_id || '_export.sql';

  l_clob := wwv_flow_utilities.export_application_to_clob(p_application_id            => p_app_id
                                                        , p_export_ir_public_reports  => p_exp_ir_pub_rep
                                                        , p_export_ir_private_reports => p_exp_ir_priv_rep
                                                        , p_export_ir_notifications   => p_exp_ir_notif);

  pro_clob_to_file(l_clob
                 , l_file_name
                 , p_ora_dir);
end pro_export_app;

Page export

procedure pro_export_app_page(p_app_id  in number
                            , p_page_id in number
                            , p_ora_dir in varchar2) as
  l_clob clob;
  l_file_name varchar2(255);
begin
  -- example: app117_page40_export.sql
  l_file_name := 'app' || p_app_id || '_page' || p_page_id || '_export.sql';

  l_clob := wwv_flow_utilities.export_page_to_clob(p_application_id => p_app_id
                                                 , p_page_id        => p_page_id);

  pro_clob_to_file(l_clob
                 , l_file_name
                 , p_ora_dir);
end pro_export_app_page;

Workspace export

procedure pro_export_workspace(p_workspace_id     in number
                             , p_include_team_dev in boolean
                             , p_ora_dir          in varchar2) as
  l_clob clob;
  l_file_name varchar2(255);
begin
  wwv_flow_api.set_security_group_id(p_workspace_id);
  -- set the apex_application.g_user global variable...
  -- the username you use as argument needs admin privileges in the workspace
  apex_custom_auth.set_user('admin');
  -- example: workspace19238827392_export.sql
  l_file_name := 'workspace' || p_workspace_id || '_export.sql';

  l_clob := wwv_flow_utilities.export_workspace_to_clob(p_workspace_id             => p_workspace_id
                                                      , p_include_team_development => p_include_team_dev);

  pro_clob_to_file(l_clob
                 , l_file_name
                 , p_ora_dir);
end pro_export_workspace;

Put it all together

The last step is to create another procedure which accepts two parameters: an application ID and Oracle directory. This procedure calls the three above wrapper procedures and queries several APEX views to fetch the pages and workspace that belong to the specified application. This way it will automatically create the individual .sql export files in the specified Oracle directory. The code itself is quite self-explanatory.

procedure pro_full_export(p_app_id  in number
                        , p_ora_dir in varchar2) as
  l_workspace_id number;

  cursor c_app_pages is
    select page_id
      from apex_application_pages
     where application_id = p_app_id;
begin
  -- export the application
  pro_export_app(p_app_id
               , 'N'
               , 'N'
               , 'N'
               , p_ora_dir);
  -- export all pages that belong to the application
  for r_app_page IN c_app_pages loop
    pro_export_app_page(p_app_id
                      , r_app_page.page_id
                      , p_ora_dir);
  end loop;
  -- get the workspace_id based on the application_id
  select workspace_id
    into l_workspace_id
    from apex_applications
   where application_id = p_app_id;
  -- export the workspace in which the application is deployed
  pro_export_workspace(l_workspace_id
                     , true
                     , p_ora_dir);
end pro_full_export;

Finally, an example on how to call the pro_full_export procedure:

declare
  p_app_id number;
  p_ora_dir varchar2(200);
begin
  p_app_id := 100;
  p_ora_dir := 'MY_ORA_DIR';

  pck_my_package.pro_full_export(p_app_id  => p_app_id
                               , p_ora_dir => p_ora_dir);
end;