Bulk Binding in Apex

Just a reminder for all those folks that just because we're saving ourselves network latency by working with Oracle Application Express, it doesn't mean we can forget some of the other nifty features of the Oracle Database.

Take working with check-boxes in tabular forms. Evil has that concept is, we can still eke out some performance benefits by using bulk binding.

In the relevant Advanced Tutorial in the Apex documentation, and in many other examples around OTN, presentations, blogs etc, we see a PL/SQL block such as this:

FOR i in 1..APEX_APPLICATION.G_F01.count
LOOP
   DELETE FROM demo_product_info
   WHERE product_id = APEX_APPLICATION.G_F01(i);
END LOOP;

With a minor tweak, we can convert this to

FORALL i IN INDICES OF APEX_APPLICATION.G_F01
   DELETE FROM demo_product_info
   WHERE product_id = APEX_APPLICATION.G_F01(i);

Benefits of which will accumulate within your database, better habits will form, and your DBAs will be happier. Further information on the benefits may be found here.

That is all :-)

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.

One more COALESCE vs NVL example to finish the week...

How many times do you think you've ever written something like this
nvl('x',user)

Small cost? What if you executed it all the time.

What if we wrote it as
coalesce('x',user)

No biggy?

Executed on my laptop with tracing on

TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if nvl('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.42

Without tracing it was still about 4 seconds.

TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if coalesce('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Why?

Check out the tkprof output...

SELECT USER 
FROM
 SYS.DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute 131072      2.21       1.04          0          0          0           0
Fetch   131072      2.81       0.53          0          0          0      131072
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   262144      5.03       1.58          0          0          0      131072

Every reference to USER in your PL/SQL or Forms code executes that statement from dual.

Sometimes we need to consider the little things, and considered using COALESCE instead of NVL.

Short-circuit Evaluations - moving away from NVL

In the experience I've had, I've found NVL such a ubiquitous expression. I started my Oracle career on 8.1.7, and it was the only expression available to filter through null expressions.

Then along came 9i. You have to look hard in the new features guide, but coalesce arrived as a "generalization of the NVL function."

To this day, I still encounter developers who have either never heard of it, or never utilise it. The more I think about the benefits of coalesce, the more I'm thinking perhaps we should stop using NVL altogether and get into the habit of using coalesce instead - regardless how how annoying it is to type.

First reason - it's more dynamic. We can determine the first non-null expression in a list. People use CASE expressions in lieu of coalesce all the time.
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Second reason - and most important - Oracle uses short-circuit evaluation, as per the ANSI standard.

Sometimes when we are comparing two values, one value could be more expensive to evaluate - perhaps it queries a table. In the scenario below I've simplified it to having a function that sleeps for the amount of seconds supplied.

10g>  create or replace function sw_delay (pn_seconds number) return number is
  2  begin
  3     dbms_lock.sleep(pn_seconds);
  4     return pn_seconds;
  5  end;
  6  /

Function created.

10g>  SET TIMING ON
10g>  -- this will always delay 1 second
10g>  select sw_delay(1) from dual;

SW_DELAY(1)
-----------
          1

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- While my function should never evaluate, the query still takes 1s
10g>  select nvl(2, sw_delay(1)) from dual;

NVL(2,SW_DELAY(1))
------------------
                 2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The same applies for nvl2
10g>  select nvl2(1, 2, sw_delay(1)) from dual;

NVL2(1,2,SW_DELAY(1))
---------------------
                    2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The ANSI standard does not evaluate unnecessary expressions.
10g>  select coalesce(2, sw_delay(1)) from dual;

COALESCE(2,SW_DELAY(1))
-----------------------
                      2

1 row selected.

Elapsed: 00:00:00.00
10g>  
10g>  -- Decode is acceptable
10g>  select decode(1,1,2,sw_delay(1)) from dual;

DECODE(1,1,2,SW_DELAY(1))
-------------------------
                        2

1 row selected.

Elapsed: 00:00:00.01
10g>  
10g>  -- As is Case 
10g>  select case when 1 = 1 then 2 else sw_delay(1) end from dual;

CASEWHEN1=1THEN2ELSESW_DELAY(1)END
----------------------------------
                                 2

1 row selected.

Elapsed: 00:00:00.00
10g>  select case 1 when 1 then 2 else sw_delay(1) end from dual;

CASE1WHEN1THEN2ELSESW_DELAY(1)END
---------------------------------
                                2

1 row selected.

Elapsed: 00:00:00.00

As commented in-line, there are occasions where there is no need to evaluate a second expression. Most programmers are taught early on to evaluate the cheapest expressions first, to save CPU time. The NVL and NVL2 expressions is not setup for this, however COALESCE, DECODE and both CASE expressions provide short circuit evaluations - once on expression returns true, there is no need to continue evaluating the superfluous entries, control returns.

The same goes for PL/SQL. Connor McDonald provides an interesting description of this here. Sometimes for the purposes of performance, it pays to be verbose.

Another version of this can be demonstrated below:

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if nvl(pn_seconds, sw_delay(1)) = 1 then
  4      return 'do this';
  5    else
  6      return 'do that';
  7    end if;
  8  end;
  9  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

Using NVL reads neatly, but regardless of the value passed, the expensive expression is always evaluated.

If it was to be rewritten with an OR operator, the expensive expression will be ignored.

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if pn_seconds is null
  4    or pn_seconds = sw_delay(1) then
  5      return 'do this';
  6    else
  7      return 'do that';
  8    end if;
  9  end;
 10  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
------------------------------
do this

1 row selected.

Elapsed: 00:00:00.01
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
------------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

What does the community think?

Please check your basic syntax and expected behaviour.

Today a colleague of mine found some indexes on a system he was working on that didn't look quite right. He checked out the definition and found something a little surprising!

create index my_index on my_table ('my_column');

The indexes had been defined incorrectly, and hence have never been utilised. For those minds that aren't working on Fridays, the index column should not be defined in quotes.

This issue can be simply demonstrated with a small test. Below I create a table, populated it with a bunch of rows, create this bad version of the index and see if it's utilised.

I've trimmed some of the output for brevity, but before my two select statements, I utilised the SQL*Plus command:
SET AUTOTRACE ON EXPLAIN

10g>  drop table chk_syntax;

Table dropped.

10g>  create table chk_syntax
  2    (col1 number(18)
  3    ,col2 varchar2(200));

Table created.

10g>
10g>  insert into chk_syntax
  2  select rownum, 'col'||rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

10g>
10g>  create index bad_syntax_i on chk_syntax ('col2');

Index created.

10g>
10g>  select * from chk_syntax where col2 = 'col2';

      COL1 COL2
---------- ----------------------------------------------------------------------
         2 col2

1 row selected.

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |   115 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CHK_SYNTAX |     1 |   115 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------

10g>
10g>  create index good_syntax_i on chk_syntax (col2);

Index created.

10g>
10g>  select * from chk_syntax where col2 = 'col2';

      COL1 COL2
---------- ----------------------------------------------------------------------
         2 col2

1 row selected.

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CHK_SYNTAX    |     1 |   115 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | GOOD_SYNTAX_I |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL2"='col2')

10g>
10g>  select * from user_ind_columns where table_name = 'CHK_SYNTAX';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ -------------------
BAD_SYNTAX_I                   CHK_SYNTAX                     SYS_NC00003$
GOOD_SYNTAX_I                  CHK_SYNTAX                     COL2

2 rows selected.

10g>
10g>  select * from user_ind_expressions where table_name = 'CHK_SYNTAX';

INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------ -------------------
BAD_SYNTAX_I                   CHK_SYNTAX                     'col2'

1 row selected.

The bad index isn't utilised, but the good one is referenced by the optimiser.

Second to this, a look at the data dictionary view user_ind_columns shows the bad index doesn't refer to an actual column - only a generated one.

This is true of function-based indexes, which is what this bad index has done. When I looked in user_ind_expressions I see the index defined as a literal string.

This is where you normally see function-based indexes such as:
CREATED INDEX surname_fn_i ON person (UPPER(surname));

This also reminds me a common open question I ask during training sessions.

What does this return:
SELECT '6 * 6' as "Area" FROM dual;

Often when writing queries, insert statements, data definition - syntax can be valid and safely executed... but it doesn't make it right.

Please check your syntax, and confirm expected behaviour.

10g> SELECT 6 * 6 as "Area" FROM dual;

Area
----------
36

1 row selected.


(Slightly) Off Topic - Science of Scale.

Sometimes relating to scale can be difficult for our humble brain. Often in Oracle we talk about millions of rows, terabytes, cartesion joins etc, but really how big is a "million". I think we really underestimate it.

Once upon a time, popular webcomic xkcd posted a logarithmic scale of the universe. This is a great way to demonstrate how far things are - as long as you understand increases in magnitude.
(click on the image to see the whole version)


Then I encountered this interactive illustration, thanks to Phil Plait's Bad Astronomy website. It was great since you got to slide from a grain of rice to the size of an atom.

Trouble is, we also wanted to go in the other direction. In the meantime, I encountered the following video from the American Natural History Musuem. It zooms from Tibet to the edge of the known universe, and back again.

Apparently if you have the time, you can create your own tour of the universe/solar system.

Recently, some clever people at Fotoshop released this flash demonstration (2010), allowing you to zoom from the edge of the known universe all the way to Plank's constant - smallest logical length of anything.

Update - 2012 version, snazzier, more interactive. Thanks Chris!
How it was done in 1968. Thanks Cary via Bad Astronomy

It's amazing to see the gaps between the sizes of some objects, and just quickly zooming from infinity to infinity. This is probably the best illustration of scale I've seen so far.

And here we are stuck on a pale blue dot.

Creating a Mail Merge with BI Publisher in Apex

Here is a simple instruction set to get you started on creating an Apex page that interfaces with BI Publisher to produce what's essentially a Mail Merge.

My presumption is you've already visited OTN to download the Oracle database software and desktop Add-In for Microsoft Word.

Step 1) Create a page to call the report.

I created a simple page that held my parameters for my query.

The only special part of this page is a branch to page zero that contained the request naming my Report Query:
PRINT_REPORT=Mail%20Merge

It would probably be better practice to use an underscore in lieu of a space, but I was curious in this case to see how it behaved.

Step 2) Create a Report Query

This step is really formed of a number of key sub steps.

a) Start the wizard
- Report query name : this is the name that forms part of your request string. I used "Mail Merge".
- Output format : PDF
- View file as : Attachment
- Session State : I chose to check this. Information supplied in your XML can be ignored.

You are then prompted for your SQL Query. I used:
SELECT name, address1 st, address2 suburb, state, postcode
FROM organisations
WHERE address1 IS NOT NULL
AND state = :P2_STATE

You should now be at a point where you can download an XML definition of your report.

Save this .xml file for the next step.

b) Create your Word document
- Leaving your Apex window sitting happily where it is, open up Microsoft Word (which has your Add-In installed)
- Select Add-Ins Oracle BI Publisher -> Data -> Load Sample XML Data
- Select your .xml file, Word will let you know the load was successful.

 

- You can then add all the fields to your blank document via Oracle BI Publisher -> Insert -> All Fields

- By default, this won't look overly pretty

- Using standard Microsoft Word editing, I shifted the fields around to resemble a standard letter
- The critical task here is to add a page break within the "for-each-region" field. This ensures each letter starts on a new page.
- I also chose to retain the application name/parameter fields on the first page as a control for the document. These fields are automatically available if you check the "Include application and session information" check-box as you create the Report Query definition.
- Save this Word document as Rich Text Format (.rtf)

c) Upload document as Report Layout
- Back in your Apex Report Query wizard, the next step allows you to define the Report Layout Source as a file based report layout.

- Confirm creation of your Report Query
Here you are presented with a URL target for use in your application. Note here the usage of page zero as the target. In the URL target for our submit button, all we needed to define was the PRINT_REPORT request:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=Mail%20Merge

How easy is that? We have a BI Publisher report linked to Apex in two steps! He he

Step 3) Iteratively modify your Word document
None of us will get the .rtf definition perfect on the first go, we may need a few iterations of this, so there's a couple of things to note here. Once we've created our Report Query, a Report Layout is subsequently added.
Unfortunately from this Report Layout page, we can only download the .rtf definition.

If we wish to modify it, we need to delete the Repory Layout, recreate the Report Layout (which is only a two step process - naming it and reloading the file), but we also need to revisit the Report Query and reset the Report Layout selection from "Use Generic Report Layout" back to our Mail Merge.

So my basic sample ultimately looked like this, opened in Adobe Reader:

The content started on the second page, the first page was my control containing session state information.
In this example, the text in black are fields populated via XML, and the text in blue is the fixed content I added.

If you have the pleasure of jumping into an environment with BI Publisher, I hope this helped you get started.