Finding stuff in ALL_VIEWS using XML

Quite some time ago when I first started blogging I wrote a post regarding limitations on searching the ALL_VIEWS dictionary view.

Simply put, if you would like to run a query such as this to find those views that contain the text "booking_no", you're not going to get very far.

SQL> select count(*)
2  from user_views
3  where text like '%booking_no%';
where text like '%booking_no%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I can't remember where I found this, but there is a way to do perform this search without pumping your data into a temporary table.

select count(*)
from user_views 
where upper(dbms_xmlgen.getxml('select text from user_views where view_name = '''||view_name||'''')) 
  like upper('%booking_no%')

It's essentially looking for "booking_no" within the result of a query that has been converted into canonical XML format.

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TEXT>SELECT r.code,b.booking_no,e.start_date,e.end_date,o.org_id,o.name
FROM   resources r,bookings b,events e,organisations o
WHERE  r.code = b.resource_code
AND    b.event_no = e.event_no
AND    o.org_id = e.org_id</TEXT>
 </ROW>
</ROWSET>

A word of warning, it is CPU (and probably memory) intensive, so you might like to limit to a particular set of schemas instead of just searching ALL_VIEWS.

Turns out XML has some nifty uses after all ;-)

Exporting deleted Apex applications

It's very rare I lose a piece of work, but recently when removing a bunch of miscellaneous applications from my workspace, I inadvertently removed one that I think I should have kept - so I thought I better go restore it.

Update: June 2016: Turns out there's a craftier solution - just create a new blank application with the same app_id, then you can use the declarative offering.

Heading over to the application export, we can in fact export applications as of x many minutes ago. I know a few people who have used this feature to save lost work, but it's dependent on a number of factors - one of which is that your application still needs to be present in the workspace!

So I got my database to turn back time so I could export the application from the database before I nuked it.

Trouble is, in my first effort, I went a little too far.

See, in the midst of battle, I wasn't sure exactly when I removed it, and how far on my little laptop db I could create time paradoxes.

I found the magic number then grabbed a copy of my application - I could have done the same thing via the SQL Developer GUI but I just happened to have this syntax on hand.

And so that's how I saved the day, well, as it turns out I did have a copy after all.

Of course, when I try to double check information about this particular feature, I find people like Tyler wrote about this some time ago. Never hurts to have a reminder :-)

-- to assist with googlers, I thought I better add the code, instead of just the image.
exec dbms_flashback.enable_at_time(systimestamp-0.17);
select wwv_flow_utilities.export_application_to_clob(120) from dual;
select * from apex_applications;

ScottWe.

Executing CLOBs as DDL

How to kill your session

11g> declare
  2  v_clob clob :=  EMPTY_CLOB(); -- initialize clob
  3  begin
  4  execute immediate v_clob;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8540
Session ID: 196 Serial number: 364

How to execute it properly

11g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;'); -- assign ddl value to clob
  5  execute immediate v_clob;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Something you couldn't do in 10g

10g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;');
  5  execute immediate v_clob;
  6  end;
  7  /
execute immediate v_clob;
                  *
ERROR at line 5:
ORA-06550: line 5, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Related OTN post.

Documentation here.

Related to an idea regarding lifecycle management.

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.