ORA-4068 and CONSTANT keyword…good and bad

Anyone that has ever coded PLSQL will be familiar with the error ORA-4068, where you had some state persisted in a session due to a package variable, and then when you change the package, the state is cleared along with an ORA-4068.  Here’s a quick example:


Session 1 

SQL> create or replace
  2  package PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL> create or replace
  2  package body  (more...)

truncated ddl in 12c (and

Just curious if anyone else is seeing this behaviour..

SQL> set long 500000
SQL> @pt "select * from v$sql where sql_id = '1km492z723vpu'"
SQL_TEXT                      : alter table scott.emp
SQL_FULLTEXT                  : alter table scott.emp
SQL_ID                        : 1km492z723vpu

Suffice to say, there’s a lot more to that SQL statement, but its lost in v$sql. Once complete, it does appear in full in v$sqlstats, but its a pain when you cant see the whole statement..

AUSOUG conference Perth

The annual two day AUSOUG conference in Perth is well underway, and so far, its been a wonderfully successful event. In particular, we Australians are notorious for not really getting into the networking thing, so conferences often have a lot of ‘awkward silences’ when sessions are not on, and we are meant to be (god forbid) *talking* to each other :-)

But this year has been different. Lots of banter, lots of discussion. Also, the (more...)

A simple 12c query with a cool result …

Its not immediately obvious the significance of this query…but trust me…you’ll love it :-)

SQL> select table_name, column_name from dba_tab_cols
  2  where column_name like '%_VC' escape ''
  3  and owner = 'SYS'
  4  order by 1,2;

TABLE_NAME                     COLUMN_NAME
------------------------------ ----------------------
ALL_VIEWS                      TEXT_VC
CDB_VIEWS                      TEXT_VC
DBA_VIEWS                      TEXT_VC
INT$DBA_VIEWS                  TEXT_VC
USER_VIEWS                     TEXT_VC


Well, the annual spectacle of enormous proportions has come to a conclusion again. And thats probably the first reason I’d recommend OpenWorld to anyone who works with Oracle who has never been to it. It’s a jaw dropping moment just to see the scale of the event, and how impressively its organised in terms of facilities, lunches, registration and the like.

But onto the technical elements, here’s my impressions of this years conference:

1) Big (more...)

Slides from OakTableWorld and OpenWorld

Thanks to anyone that came along to my sessions at OpenWorld.

You can review the slides via the links below:




Openworld day minus 3

Its day minus 3, or day +1 depending on your point of view at Openworld 2014.

I’m here a few days early for the Ace Director briefings, where a selection of product managers provide an insight into what is coming in the Oracle world, either at Openworld or in the coming year.

I must admit, I arrived to the briefings somewhat sceptical because a couple of years ago, when I was last at the briefings, (more...)

Openworld bound..

I’ll be in Openworld again this year, and have a couple of speaking slots…

I’ll talk about new features for developers in 12c on Sunday, Sep 28, 2:30 Moscone South 303


and of course, at the awesome Oaktable world..


Drop in, learn some cool things, or just pop up and say Hello!

MERGE and IOT’s ….. unhappy bedfellows

Anyone who has used Oracle for a while will be familiar with the Parent/Child locking "issue" when it comes to tables and indexes on foreign keys.  For many years you’d hear people crying "bug" etc but thankfully most now know the reason, and accept it as sensible behaviour.

But lets take a look at a slight variation on that theme.

Lets start with a table called "LOC" which will be our parent table in (more...)

12c – Nested tables vs Associative arrays

This was going to the be the immediate follow up to my previous post, but came out and I got all excited about that and forgot to post this one :-)

Anyway, the previous post showed how easy it is to convert between nested tables and associative arrays.  The nice thing in 12c is that this is no longer needed – you can query the associative arrays directly

SQL>  (more...)