wwv_flow_utilities.export_application_to_clobto 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);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.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
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.