Overview
The Purpose of the backup is to create a copy of data that can be recovered in the event of a primary data failure. Primary data failures can be the result of hardware or software failure, data corruption, or a human caused event, such as a malicious attack (virus or malware), or accidental deletion of data. Backup copies allow data to be restored from an earlier point in time to help the business recover from an unplanned event.
In this Blog we will see how effectively we can use Oracle apex Dictionary views to Mange the backups.
Technologies and Tools Used
The following technology has been used to achieve the expected output.
- Oracle Apex
- SQL/PL SQL
Use Case
In Oracle apex we are having automatic backup option. Once the developer enables the automatic backup option in application.
Automated backups are performed as part of daily maintenance. Only applications that have been modified will be backed up.
By this method, we can get all those backup files in the “APEX_WORKSPACE_FILES” apex dictionary views.
Using the “APEX_WORKSPACE_FILES” users can access the files from front end of the application itself. By this, the developers help not needed to take the backup. Users themselves or admin users can manage the backups.
However, Only the 25 most recent application backups are retained. Older backups will be deleted to make room for new ones.
To overcome this developer can create the local table and maintain the applications backups using scheduler. By this users or admin user will have access to all the backup files without developers help.
Like this we can take the backup of static files also. The static files backup will be maintained in the “APEX_APPLICATION_STATIC_FILES” apex dictionary views.
Other than automatic backup, we can do manual backup also. To manually back up the application, outside of daily maintenance.
The Manual backup can be achieved in the front end of the application by using some custom solution.
This blog explains how to achieve this requirement.
Architecture
The Automated backups application will be stored in the “APEX_WORKSPACE_FILES” apex dictionary views.The report can be developed to display the details with download option.
The below steps explain the details,
Step 1:
Create the Interactive Report with the below query.
select WORKSPACE_ID,WORKSPACE_NAME,WORKSPACE_DISPLAY_NAME,FILE_ID,APPLICATION_ID,
APPLICATION_NAME,FILE_NAME,MIME_TYPE,FILE_SIZE,CREATED_BY,EMAIL,CREATED_ON,
FILE_TYPE,Length(DOCUMENT) BLOB_LENG,TEMP_FILE_SESSION_ID
from APEX_WORKSPACE_FILES;
Step 2:
Set the BLOB_LENG column as Download BLOB type.
Step 3:
Set the BLOB attributes as per the below screen shot.
The Manual Backup of the application can be achieved in front end of the application by below steps.
Step 1:
Create the page item as select list using the below query and create the button as Download.
SELECT distinct APPLICATION_NAME D,APPLICATION_ID R
FROM apex_applications;
Step 2:
Create the tables as below.
CREATE TABLE “APP_EXPORT”
( “APP_FILE” CLOB,
“APP_ID” VARCHAR2(50)
) ;
Step 3:
Create the page process as below.
declare
l_files apex_t_export_files;
contents CLOB;
CURSOR get_app
IS
SELECT application_id
FROM apex_applications
WHERE application_id = :P10_APP_ID;
BEGIN
DELETE FROM app_export;
commit;
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 app_export (app_id, app_file)
VALUES (i.application_id, contents);
END LOOP;
END;
Step 4:
Create the after process branch as below.
Step 5:
Create the function as below.
create or replace FUNCTION clob_to_blob (i_clob_text IN CLOB)
RETURN BLOB
IS
l_blob_file BLOB;
l_lob_length PLS_INTEGER := DBMS_LOB.getlength (i_clob_text);
l_position PLS_INTEGER := 1;
l_buffer_size RAW (32767);
BEGIN
DBMS_LOB.createtemporary (l_blob_file, TRUE);
DBMS_LOB.open (l_blob_file, DBMS_LOB.lob_readwrite);
LOOP
l_buffer_size :=
UTL_RAW.cast_to_raw (
DBMS_LOB.SUBSTR (i_clob_text, 16000, l_position));
IF UTL_RAW.LENGTH (l_buffer_size) > 0
THEN
DBMS_LOB.writeappend (l_blob_file,
UTL_RAW.LENGTH (l_buffer_size),
l_buffer_size);
END IF;
l_position := l_position + 16000;
EXIT WHEN l_position > l_lob_length;
END LOOP;
RETURN l_blob_file;
END clob_to_blob;
/
Step 6:
Create another page with page with Pre-Rendering before header process and page item as below.
DECLARE
l_clob_text CLOB;
l_blob_file BLOB;
l_blob_2 BLOB;
BEGIN
SELECT app_file
INTO l_clob_text
FROM App_Export
where app_id=:P9_APP_ID;
l_blob_2 := clob_to_blob (l_clob_text);
sys.htp.p(‘Content-Disposition: attachment; filename=”‘ ||:P9_APP_ID||’.sql’ || ‘”‘ );
sys.htp.p(‘Cache-Control: max-age=3600’); — if desired
sys.owa_util.http_header_close;
sys.wpg_docload.download_file(l_blob_2);
apex_application.stop_apex_engine;
END;
Step 7:
Create the branch to redirect to previous page as below.
Screen Shot
Using the above steps we can create the report with download option by automatic backup of application and we can create the instant download option of selected application in oracle apex application.
Out Put:
The report to display the automatic backup applications in the “APEX_WORKSPACE_FILES” with download option.
The customized solution to download the manual backup from front end of the application.
