How to Export Oracle APEX Application automatically on a Daily Basis
Today I got the solution to export the APEX application automatically instead of manually way. In this tutorial, you will learn how to export the 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 that you want to export.
CREATE TABLE export_clob
(
app_export CLOB,
app_id VARCHAR2 (5)
);
- Create a Procedure to save the exported file into the 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
- Clob to Blob using apex_webservice
- Blob to Clob using DBMS_LOB
- CLOB to BLOB using PLSQL
- BLOB to CLOB using APEX_WEB_SERVICE
- Now Need to write the SQL file into the Directory “APEX_TEMP ” using the 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;
/
- The below procedure will export the application save it into the table and then write the saved file into the 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;- OR
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;- In the final Section, we need to create a job Job Scheduler for exporting applications on a daily basis and this scheduler will call the 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;
1 Comment