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
- 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 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;- 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 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;
1 Comment