Associative arrays and Nested tables

A common criticism of PLSQL is that the "original" array datatype, now called associative arrays are perfect for passing stuff back and forth to 3GL environments (for example .Net),  but canno be used within SQL natively, for example:

SQL> create or replace
  2  package BLAH is
  3    type num_list is table of number index by pls_integer;
  4    type str_list is table of varchar2(30) index by pls_integer;
  6  procedure ODP_PROC(n out num_list, s out str_list);

No Hakan factor for IOT

Sadly there seems to be no concept of the Hakan factor for an IOT.

I have an application which merges into an IOT, the merge incrementally populating a swag of initially null columns, hence growing the rows in size.  Some simple benchmarking shows the overhead of this versus merging into a table with pre-populated values:

SQL> create table T1
  2   ( x int primary key,
  3     y1 number(10),
  4     y2 number(10),
  5     y3 number(10),

12c nasty with remote query optimization

We have a fairly common query process, where we run a MERGE command to compare a remote table to a local copy of it, as "poor mans" Golden Gate to bring that table up to date on a regular basis.  [Editors note: Writing MERGE's is more complicated but a lot cheaper than Golden Gate :-)]

After an upgrade to 12c, the performance of some of the MERGE’s went very bad…and you can see what (more...)

Testing…the surgeon’s approach

I played a lot of volleyball in a bygone life :-) and subsequently ruined my knees to the extent that I needed surgery. I got a shock when the surgeon (after a series of x-rays and checks) said to me: "Of course, we’ll only know once we’re in there".

So here’s a body part (a knee) that’s had hundreds of thousands of years to evolve, so you’d expect that knees are pretty much the same (more...)

Upgrade to 12c … credentials

We did a "real" upgrade to 12c this weekend, where "real" means a production system, as opposed to my laptop, a play VM etc etc :-)

It all went relatively smoothly except for one interesting thing, that I can’t 100% say was caused by the upgrade, but it would appear to be the case.

After the upgrade, our scheduler external jobs started failing.  A quick look in the alert log revealed:

Sun Jun 29  (more...)

Dumb triggers part 2 – session level control

In the previous post, I pontificated about triggers that "lock you in" to having them fire, which can create dramas when it comes to doing data patching.

Maybe you can design your application around this, but if you cant, the last thing you want to be doing is having to take an outage so that you can disable the trigger whilst you do your data maintenance.  Ideally you want the trigger to fire (more...)

Really dumb triggers

Some people hate triggers, some people love triggers…

I am not that opinionated on them in either direction, but one thing I do hate, whether it be a trigger or not, is dumb code.  And today’s post just happens to be about dumb code in a trigger.

Consider this simple trigger (you see these everywhere pre 12c):


An accusatory error message

I found this hilarious

SQL> startup
ORACLE instance started.

Total System Global Area 1469792256 bytes
Fixed Size                  2402776 bytes
Variable Size             536872488 bytes
Database Buffers          922746880 bytes
Redo Buffers                7770112 bytes
Database mounted.
ORA-19821: an intentionally corrupt log file was found

Really ? I intentionally corrupted my log file ?  I dont think so !


SQL injection

Another big public username and password leak…

Some good reading on how it was done, and thus ensuring your code isn’t prone to SQL injection here:

Pluggable database and restricted sessions

Once you get into pluggable database territory, you might need to check your usage of "alter system enable restricted session", because unless you’ve patched, there’s a little bug which lets you enable restricted session, but wont let you get out of it ! :-)

For example:

SQL> alter session set container = pdb12;
Session altered.
SQL> alter system enable restricted session;
System altered.
SQL> select logins from v$instance;
SQL> alter system disable restricted  (more...)