How to Export Oracle APEX Application automatically on Daily Basis

How to Export Oracle APEX Application Automatically on Daily Basis

Today i got the solution to export apex application automatically instead of manual way. In this tutorial, you will learn how to export Apex Application with these simple steps which i have given below.

• First create your Directory "APEX_TEMP " and Grant for read and write.

CREATE DIRECTORY apex_temp AS 'C:\APEX_TEMP';
GRANT READ, WRITE ON DIRECTORY apex_temp TO admin;

• Now Create a Table for Storing Application details which you want to export.

CREATE TABLE export_clob
(
   app_export   CLOB,
   app_id       VARCHAR2 (5)
);

• Create Procedure to save exported file into Directory,You can easily get this code from Oracle base.

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

1. Clob to Blob using apex_webservice
2. Blob to Clob using DBMS_LOB
3. CLOB to BLOB using PLSQL
4. BLOB to CLOB using APEX_WEB_SERVICE

•  Now Need to write Sql file into Directory "APEX_TEMP " using previous Procedure.

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;
/

• In this section will export application and save into table and then write saved file into directory.

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; 

• In the final Section we need to create a job  Job Scheduler for exporting application on daily basis and in this scheduler will call above procedure "EXPORT_APPLICATION ".

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 Versoiin control in Oracle Apex');
END;