Automating Daily Backups: How to Schedule Oracle APEX Application Exports

March 18, 2020Posted inAPEX

How to Automate Daily Exports for Your Oracle APEX Application

Today, I’m excited to share a solution for automating the export of Oracle APEX applications, eliminating the need for manual exports. This tutorial will guide you through a streamlined process to set up automatic daily exports of your APEX applications. Follow these simple steps to ensure your application data is backed up consistently and efficiently.

1. Create a Directory and Set Permissions

  • Create Directory: First, you need to create a directory for storing the exported files. You can name it APEX_TEMP or any name of your choice.
  • Grant Permissions: Ensure that the Oracle database user running the export process has read and write permissions on this directory.
Copy Code
CREATE DIRECTORY apex_temp AS 'C:\APEX_TEMP';
GRANT READ, WRITE ON DIRECTORY apex_temp TO admin;
Replace C:\APEX_TEMP with the actual path where you want to store the exported files, and APEX_TEMP with the appropriate Oracle APEX user.
2. Create a Table for Storing Application Export Details


Objective: Set up a table to hold the export data for your Oracle APEX applications. This table will store the exported application content in a CLOB (Character Large Object) format along with a reference to the application ID.

Copy Code
CREATE TABLE export_clob
(
    app_export   CLOB,           -- Column to store the exported application data
    app_id       VARCHAR2(5)     -- Column to store the application ID
);

app_export: This column will store the actual exported application data as a CLOB.
app_id: This column will store the ID of the application being exported, which helps in identifying and managing multiple applications.

  • Objective: Develop a PL/SQL procedure to automate the export of your Oracle APEX application and save it into the directory you created. This procedure will handle the export process and store the export data in the export_clob table.
Copy Code
CREATE OR REPLACE PROCEDURE blob_to_file (p_dir    IN VARCHAR2,
                                          p_file   IN VARCHAR2,
                                          p_blob   IN BLOB)
AS
   l_file       UTL_FILE.file_type;
   l_buffer     RAW (32767);
   l_amount     BINARY_INTEGER := 32767;
   l_pos        INTEGER := 1;
   l_blob_len   INTEGER;
BEGIN
   l_blob_len := DBMS_LOB.getlength (p_blob);
   l_file :=
      UTL_FILE.fopen (p_dir,
                      p_file,
                      'WB',
                      32767);

   WHILE l_pos < l_blob_len
   LOOP
      DBMS_LOB.read (p_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
   END LOOP;

   UTL_FILE.fclose (l_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      RAISE;
END blob_to_file;

Check out blob to clob and clob to blob conversions

Copy Code
CREATE OR REPLACE PROCEDURE write_to_dir
IS
   CURSOR get_clob
   IS
      SELECT app_export, app_id, clob_to_blob (app_export) b_file
        FROM export_clob;
BEGIN
   FOR i IN get_clob
   LOOP
      blob_to_file (
         'APEX_TEMP ',
         TO_CHAR (SYSDATE, 'DDMONYYHH24MISS') || '_' || i.app_id || '.sql',
         i.b_file);
   END LOOP;
END write_to_dir;
/

  • Automate the process of exporting an Oracle APEX application, saving the export data to a database table, and writing the file to a specified directory.
Copy Code
CREATE OR REPLACE PROCEDURE export_application
IS
   l_files    apex_t_export_files;
   contents   CLOB;

   CURSOR get_app
   IS
      SELECT application_id
        FROM apex_applications
       WHERE application_id = 101;
BEGIN
   DELETE FROM export_clob;

   FOR i IN get_app
   LOOP
      l_files :=
         apex_export.get_application (p_application_id => i.application_id);
      contents := l_files (1).contents;

      INSERT INTO export_clob (app_id, app_export)
           VALUES (i.application_id, contents);
   END LOOP;

   COMMIT;

   BEGIN
      write_to_dir;
      COMMIT;
   END;
END;
  • OR
Copy Code
CREATE OR REPLACE PROCEDURE export_application
IS
   CURSOR get_app
   IS
      SELECT application_id
        FROM apex_applications
       WHERE application_id IN (101, 108);
BEGIN
   DELETE FROM export_clob;

   FOR i IN get_app
   LOOP
      INSERT INTO export_clob (app_id, app_export)
              VALUES (
                        i.application_id,
                        wwv_flow_utilities.export_application_to_clob (
                           i.application_id));
   END LOOP;

   COMMIT;

   BEGIN
      write_to_dir;
      COMMIT;
   END;
END;
  • To set up a job scheduler in the Oracle Database that automatically exports an APEX application daily, follow these steps. This process involves creating a job that executes the EXPORT_APPLICATION procedure at a specified time each day.
Copy Code
CREATE SEQUENCE exp_apax_seq START WITH 1 INCREMENT BY 1;

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name          => 'VIKAS' || exp_apax_seq.NEXTVAL,
      job_type          => 'PLSQL_BLOCK',
      job_action        => 'begin
                           EXPORT_APPLICATION;
                           end;',
      start_date        => SYSDATE, --+1/24/59, -- sysdate + 1 minute
      repeat_interval   => 'freq=MINUTELY;interval=5',
      enabled           => TRUE,
      auto_drop         => TRUE,
      comments          => 'Application Versoin control in Oracle Apex');
END;

You might Like

Tags: