Wednesday 17 February 2010

Automating Apex Backups from the DB

I was talking with Penny Cookson recently about the supplied procedure wwv_flow_utilities.export_application_to_clob to 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);
  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
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.

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.

18 comments:

Jeffrey Kemp said...

Nice! I'll definitely be stealing that and giving it a try.

Scott Wesley said...

Stealing? Nay, it is but a mere gift ;-)

Scott Wesley said...

Depending on OS, you may need to use UTL_FILE.PUT_LINE, instead of UTL_FILE.PUT.

SQL updated

Scott Wesley said...

Updated to
-- ignore some workspaces
-- limit query to connected user
-- actual parameter name changes

Eric Cloutier said...

Nice works!

VJ said...

Scott,

Gr8 job. I owe you a beer :) :)

Scott Wesley said...

Careful, I'll hold you to that - I like EB ;-)

Anonymous said...

Scott,

Any time for EB beer.
Ping me when you are in Chicago :)

VJ

Damir Vadas said...

I allways wondered:

UTL_FILE.FCLOSE(lf_file);
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
should be:
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_FILE.FCLOSE(lf_file);

Because first statement might clear "SQLERRM" content.

Scott Wesley said...

Sounds fair enough - adjusted.

Lovneesh said...

When i use this procedure on linux then I get an exception that is
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
at
DBMS_LOB.READ (lc_clob,ln_amount, ln_pos, lc_buffer);

I don't know, why it is so, can you help?

Scott Wesley said...

MY first question would be regarding character set. This procedure uses VARCHAR2(n BYTE). If you have characters outside the standard set that need more information, then you might reach that ORA-06502. Try using VARCHAR2(n CHAR)

My first guess?

Lovneesh said...

After using Varchar2(n char), same error shown ORA-06502.

This error shown when I use this procedure on linux but when I use this on Windows it works fine.

Scott Wesley said...

Obviously something OS dependent - maybe relating to line feeds.

Doco suggests LF would be included, but try lowering the value of ln_amount. The API default is 1024.

Lovneesh said...

It works, after set limit ln_amount to 1024 and increase the size of tablespace.

But after execute that procedure line is break because
utl_file.put_line(lf_file, lc_buffer);
like
Varchar is insert into file as:

Va
rchar

So, I used UTL_FILE.PUT but after executing procedure, only first buffer was pasted at file but loop executed as required.

Scott Wesley said...

I remember battling this issue, but can't recall a solution off the top of my head.
This would likely be a common issue - I'd try searching for anything related to output using UTL_FILE

neverflw said...

Things have change since:

CREATE OR REPLACE DIRECTORY apex_backup AS 'C:\APEX_BACKUP'

ORA-01031: insufficient privileges ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210100", line 673 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210100", line 659 ORA-06512: at "APEX_210100.WWV_FLOW_DYNAMIC_EXEC", line 1855



Scott Wesley said...

Or is it you
- don't have privileges to create directory - unlikely based on error
- db doesn't have privileges on that folder?
Will also depend on who you're running this as, and is it on your own server?
Seems like you're doing it in the SQL workshop.