Automating Apex Backups from the DB

I was talking with Penny Cookson recently about the supplied procedure wwv_flow_utilities.export_application_to_clob to automatically export your applications from the database. We were wondering if you could also import an application from a CLOB, but there is nothing yet supplied.

What I've come up with in the meantime, however, is a PL/SQL procedure that could be a starting point in automating your backups from the database via a scheduled job.

This procedure copies your application into a table as a CLOB, and optionally places a copy of that CLOB as a SQL file in your file system. I don't think I need to talk to much about the contents here as hopefully everything interesting is self evident in the in-line comments.

Last edited 2012/1/5

-- Create an oracle directory the describes your desired file location
CREATE OR REPLACE DIRECTORY apex_backup AS 'E:\APEX_BACKUP';

-- Create a table that stores information you may be interested in about your backup
DROP TABLE apex_exports;
CREATE TABLE apex_exports
  (application_id     NUMBER
  ,application_name   VARCHAR2(255)
  ,alias              VARCHAR2(255)
  ,export_clob        CLOB
  ,export_date        DATE);

CREATE OR REPLACE PROCEDURE export_apex
  (pc_export_to_file  IN  VARCHAR2    DEFAULT 'Y'
  ,pc_directory       IN  VARCHAR2    DEFAULT 'APEX_BACKUP'
  ,pn_application_id  IN  PLS_INTEGER DEFAULT NULL -- Optionally restrict
  ) IS
-- ***************************************************************
-- Scott Wesley - Sage Computing Services - Feb-2010
-- Export selected apex applications to a table, and to file if requested
-- ***************************************************************
  lf_file            UTL_FILE.FILE_TYPE;
  lc_buffer          VARCHAR2(32767);
  ln_amount          PLS_INTEGER;
  ln_pos             PLS_INTEGER;
  lc_clob            CLOB;
  lc_filename        VARCHAR2(200);
BEGIN
  -- You could easily invisage extending this, replacing pn_application_id with
  -- any number of columns listed in this apex view.
  << backup_applications >>
  FOR r_rec IN (SELECT a.workspace, a.application_id, a.application_name, a.alias, a.application_group
                      ,SYSDATE snapshot_date
                FROM   apex_applications a
                WHERE  a.application_id = COALESCE(pn_application_id, a.application_id)
                AND    a.workspace NOT IN ('INTERNAL','COM.ORACLE.APEX.REPOSITORY')
                AND    a.owner = USER
                ) LOOP

    lc_clob := wwv_flow_utilities.export_application_to_clob
                (p_application_id       => r_rec.application_id);
                -- Apex 32 only p_export_saved_reports => 'N'
                -- Apex 40  p_export_ir_public_reports, p_export_ir_private_reports, p_export_ir_notifications

    -- Backup application to table
    -- This will need to be separate from the procedure call otherwise you get
    -- ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
    INSERT INTO apex_exports (application_id, application_name, alias, export_clob, export_date)
    VALUES (r_rec.application_id, r_rec.application_name, r_rec.alias, lc_clob, r_rec.snapshot_date);

    IF pc_export_to_file = 'Y' THEN
      -- Having your application backed up in a table on the same DB will
      -- not necessarily save it.
      -- Ideally your exports should be located on a network drive that is
      -- regularly backed up and easily accessable

      -- Determine filename used for backup and open that file for write
      -- If you were looping through the apex view, you could make this more descriptive
      lc_filename := 'f_'||r_rec.application_id||'_'||r_rec.alias
                     ||TO_CHAR(r_rec.snapshot_date,'_YYYYMMDDHH24MISS')||'.sql';
      ln_pos      := 1;
      ln_amount   := 32767;
      lf_file := UTL_FILE.FOPEN
                   (location     => pc_directory
                   ,filename     => lc_filename
                   ,open_mode    => 'w'
                   ,max_linesize => ln_amount);

      -- Copy the data from the clob extracted to the file created.
      << export_file >>
      BEGIN
        << lob_read >>
        LOOP
          DBMS_LOB.READ (lc_clob, ln_amount, ln_pos, lc_buffer);
          UTL_FILE.PUT_LINE(lf_file, lc_buffer);
          ln_pos := ln_pos + ln_amount;
        END LOOP lob_read;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        -- End of CLOB
        NULL;
      END export_file;
      UTL_FILE.FCLOSE(lf_file);

      DBMS_OUTPUT.PUT_LINE(ln_pos||' bytes written to file: '||lc_filename);

    END IF; -- export to file

  END LOOP backup_applications;

  COMMIT;

EXCEPTION WHEN OTHERS THEN
  -- Ideally expected forms of UTL_FILE exceptions would be handled here.
  -- Those you don't expect may occur should be left to propogate.
  -- How this exception handler is modified would depend on implementation method
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  UTL_FILE.FCLOSE(lf_file);

ROLLBACK;
  RAISE;
END;
/

sho err 

-- executed with a temporary restriction on application_group = 'Standard'
sw10g> exec export_apex(pc_export_to_file => 'Y')
472686 bytes written to file: f_100_DEMO_APP_20100217140909.sql
273679 bytes written to file: f_130_STANDARD_20100217140909.sql
275875 bytes written to file: f_110_STANDARD_JQUERY_20100217140909.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.31

This procedure is by no means fully tested, but it does lend itself to customisation for your particular purpose. I ran it successfully in my Oracle 10gR2 Apex 3.2.1 laptop environment.

Another "want" of mine that has come out of this, which really re-iterates past thoughts - is to have more thorough documentation of the utilities available to Oracle Application Express developers. Perhaps on a par with the Oracle Database PL/SQL Packages and Types Reference book.

If you would like to automate the backup/export of your Oracle Apex applications from the command line, I would suggest looking either here or here. A thank you is also due to Tim Hall for this page, so I didn't have to think about the CLOB export component. Let's not reinvent the wheel, everybody.