OGh APEX day 2011

Yesterday I attended OGh's APEX day. Previous year was already pretty successful with 150 people attending. This year the maximum of 150 people was reached in only a few days, so we scaled up the event to a maximum of 250 people, and in the end we still had to disappoint several people. And this year we had several sponsors as well: Oracle, Ciber, Transfer Solutions and Itium. These must be signs that APEX is becoming increasingly popular here in the Netherlands.

The day started with three plenary sessions. The first one was by Tony Andrews and Nigel Blair who told us about their huge APEX project for Northgate Revenues and Benefits. They sell software to local counsils and their application contained approximately 1500 Forms. They had moved from character mode, to Oracle Forms to Oracle Webforms with their application. Their users complained about Webforms of which the user interface deviated too much from their normal internet experience. The sales win rate dropped from 1 out of 2 to 1 out of 4 or less. With their new APEX application they focussed on the user interface again and are now back at winning 50 to 60% of the deals. It was also good to see how Tony solved several technical problems of which some are now part of APEX 4. This session was a great show case that APEX is certainly very much capable for large, enterprise level applications.

The next session was Hilary Farrell's session about APEX 4.1. Hilary is part of the APEX development team. It always amazes me how much great work this team gets done with such a relatively small team. The last years I think I've seen almost every team member present somewhere: all highly competent and passionate people. And Hilary is no exception. Lots of small improvements are on their way in APEX 4.1, of which I think I'm going to like the improved error handling the most. You can read more about the new version in this statement of direction.

Next up were Dimitri Gielis and John Scott with "APEX Evangelists way of working in APEX Projects". They showed how they handled several aspects of a new APEX project. From Ant scripts to generate a fresh projectstructure in Subversion. Continuous integration, their "Glitchi" project management APEX-tool and Balsamiq, with which they design the user interfaces together with the client.

In the afternoon I saw Jasper Alblas' session demonstrating Eneco's new debtor application. I have worked for more than six years at Eneco, so I was eager to see this session. Very informative session and a nice looking application. Well done, Jasper!

The second afternoon session I attended was John Scott's "Building APEX 4 Plugins". I always love John's presentations. He always presents with humour and makes seemingly complex subjects appear simple so you'll end up thinking "hey, I can do that myself without too much ado". I especially liked his Google Translate demos.

The last session I attended was Hans Wijnacker's Golfscores.eu session. Again a well delivered session about an APEX-application that keeps track of golfscores at tournaments. If you are organizing a golf tournament, you can hire them to take care of the IT. I saw Hans faced some similar challenges that I've faced with my own APEX application for Tourpoules (currently offline by the way, waiting for 11g XE to be released ...).

I had a great time and I want to thank OGh and especially Natalie Rohlof, who organized the event, for the fantastic day. I hope Learco and me will get the chance to setup a program for a third edition in 2012.

Open cursor paranoia

Most PL/SQL developers will likely have witnessed this phenomenon several times during their career. But only in other people's code, of course :-). I'm talking about PL/SQL code where every program unit ends like this:

exception
when others then
  if c%isopen
  then
    close c;
  end if;
  raise;
end;

where lines 3 to 6 are repeated for every cursor in the block above.

Proponents of open cursor paranoia justify it as defensive programming. Even when you open-fetch-close a cursor, the fetch could raise an exception and the close statement would not execute, leaving the cursor open, they say. A next execution would raise the dreaded "ORA-06511: PL/SQL: cursor already open".

Let's examine this claim. First by defining about which type of cursor we're talking about, since the term "cursor" is an overloaded one. Here I am talking about explicit session cursors in PL/SQL. The ones you declare and manage yourself. Now suppose you have this package:

SQL> create package pkg
  2  as
  3    procedure p;
  4  end pkg;
  5  /

Package created.

SQL> create package body pkg
  2  as
  3    procedure p
  4    is
  5      cursor c is select dummy from dual;
  6    begin
  7      open c;
  8    end p;
  9  end pkg;
 10  /

Package body created.

What will the next piece of code do? Will it raise an ORA-06511: cursor already open?

SQL> begin
  2    pkg.p;
  3    pkg.p;
  4  end;
  5  /

No it doesn't:

PL/SQL procedure successfully completed.

An explicit cursor variable has a scope, just like every other variable. Its scope here is the procedure. When the procedure ends, the cursor variable is gone and you can safely do an "open c" again. Note that PL/SQL has a PL/SQL cursor cache which keeps the cursor silently open in the background, so that a reopen of this cursor will not cause a reparse, not even a soft parse:

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL'
  4  /

CURSOR_TYPE
----------------------------------------------------------------
PL/SQL CURSOR CACHED

1 row selected.

By the way, in 11g, Oracle has added 3 extra columns to the V$OPEN_CURSOR view. The new column CURSOR_TYPE is very informative as you can see above.

The cursor type is PL/SQL CURSOR CACHED, because the SESSION_CACHED_CURSORS parameter is on its default of 50 and the cursor was closed in the PL/SQL code. When you switch this parameter off by setting it to 0, the entry disappears from V$OPEN_CURSOR, and the cursor is closed in the background as well:

SQL> alter session set session_cached_cursors = 0
  2  /

Session altered.

SQL> begin
  2    pkg.p;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL'
  4  /

no rows selected

SQL> alter session set session_cached_cursors = 50
  2  /

Session altered.

Is there really no way that closing open cursors in the exception handler is useful? Maybe it once made sense in an earlier version? If you know this was the case once, please leave a comment. And it could be useful when the scope of the cursor is bigger than just the procedure, for example when it's the declared globally in the package. In this case the package state holds the state of the cursor variable, so with this package:

SQL> create or replace package body pkg
  2  as
  3    cursor c is select dummy from dual
  4    ;
  5    procedure p
  6    is
  7    begin
  8      open c;
  9    end p;
 10  end pkg;
 11  /

Package body created.

, you do get the ORA-06511:

SQL> begin
  2    pkg.p;
  3    pkg.p;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "RWIJK.PKG", line 3
ORA-06512: at "RWIJK.PKG", line 8
ORA-06512: at line 3

And the cursor_type attribute of v$open_cursor now says:

SQL> select cursor_type
  2    from v$open_cursor
  3   where sql_text = 'SELECT DUMMY FROM DUAL '
  4  /

CURSOR_TYPE
----------------------------------------------------------------
OPEN-PL/SQL

1 row selected.

But this raises the question why you would ever want to have a globally defined cursor. Recently I've seen more than one application with so called cursor packages. Packages containing only cursors in their specification, as helper packages for the other non cursor packages in the application. A truly bad idea, not only for not hiding the implementation details from the caller, but also because once the package is called, the package has a state and the cursor variables don't get closed automatically, making the application vulnerable for ORA-06511's. Although, I can imagine a cursor package in a situation where a mid-tier application wants to handle record sets without having to deal with the SQL. I don't have experience with this scenario myself though.

Odds are that when you see "if c%isopen then close c; end if;" constructs being used abundantly throughout the code, most of them can happily be removed.

Runtime error ORA-01031: insufficient privileges

After a new version of software was installed in production, the end users reported a runtime error: ORA-01031: insufficient privileges, when selecting from a view. The developers of the code were investigating the problem and half way through, they asked me to have a look at the problem. I saw a function from schema3, which was used in a view in schema2, which was used by schema1. I had just finished learning their situation, when they had tried granting an EXECUTE-privilege on the function with grant option, which solved the error. The privilege with grant option was quickly granted in production and everything went back to normal and all interest in the problem vanished. But I was puzzled: how could that missing grant option lead to the error at runtime. It should not have been possible to grant the SELECT-privilege to schema1 at compile/install time, or at least I thought so. So I did a little investigation.

SQL> create user rwijk3 identified by rwijk3
  2  /

User created.

SQL> grant create session, create procedure to rwijk3
  2  /

Grant succeeded.

SQL> create user rwijk2 identified by rwijk2
  2  /

User created.

SQL> grant create session, create view to rwijk2
  2  /

Grant succeeded.

SQL> create user rwijk1 identified by rwijk1
  2  /

User created.

SQL> grant create session to rwijk1
  2  /

Grant succeeded.

SQL> conn rwijk3/rwijk3@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> create function f return number
  2  is
  3  begin
  4    return 42;
  5  end f;
  6  /

Function created.

SQL> grant execute on f to rwijk2
  2  /

Grant succeeded.

SQL> conn rwijk2/rwijk2@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> create view v
  2  as
  3  select rwijk3.f() x
  4    from dual
  5  /

View created.

SQL> grant select on v to rwijk1
  2  /
grant select on v to rwijk1
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'RWIJK3.F'

This is the scenario I expected. Granting the select privilege leads to an error message at compile/install time. But our scenario was a little different than this clean install: the view already existed without the function call and the privilege without the grant option was already granted. Like this:

SQL> show user
USER is "RWIJK2"
SQL> drop view v
  2  /

View dropped.

SQL> create view v
  2  as
  3  select 42 x
  4    from dual
  5  /

View created.

SQL> grant select on v to rwijk1
  2  /

Grant succeeded.

And user rwijk1 could happily query the view of course:

SQL> conn rwijk1/rwijk1@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> select x
  2    from rwijk2.v
  3  /

         X
----------
        42

1 row selected.

The new version introduced the function call:

SQL> create or replace view v
  2  as
  3  select rwijk3.f() x
  4    from dual
  5  /

View created.

Which now succeeds, even though select privileges on this view were already granted to schema1. Oracle didn't raise an ORA-01720, like it did in the fresh install scenario. So Oracle does not perform a check whether all already granted privileges remain effective. It also doesn't remove the privileges as user RWIJK1 can describe the view because of the SELECT privilege without the grant option:

SQL> conn rwijk1/rwijk1@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> desc rwijk2.v
 Name                                    Null?    Type
 --------------------------------------- -------- ---------------------------
 X                                                NUMBER

It just issues a runtime error message when you issue a select against the view:

SQL> select x
  2    from rwijk2.v
  3  /
  from rwijk2.v
              *
ERROR at line 2:
ORA-01031: insufficient privileges

An ORA-01031 during the parse phase, which you can also see in the trace file:

PARSE ERROR #47952083822528:len=25 dep=0 uid=114 oct=3 lid=114 tim=1299371043385365 err=1031
select x
  from rwijk2.v

When doing a "CREATE OR REPLACE VIEW", and the view is used inside a program unit, like a package, procedure or function, these dependent program units are marked invalid so they need to be compiled again against the new view definition.

When doing a "CREATE OR REPLACE VIEW", and the view has instead of triggers defined against the view, the triggers are removed when the view definition is replaced. And you need to install the instead of triggers again.

And when doing a "CREATE OR REPLACE VIEW", and privileges on this view were granted before replacing the view definition, these privileges are not re-evaluated when the view definition is replaced.