Countdown Timer Part II

Once before I had a countdown (well, countup) timer to mark the time since my last production incident (one where I screwed something up). It was a way to poke fun at myself but also a steady reminder to be careful.

In that vain, I've created a new one marking the consecutive time I've been employed! As noted in my previous post, I've had difficulty holding on to jobs this past year. Three times in fact. Fired once, laid off twice and brought back twice (by the same company).

Here's to not having to reset this effin clock in 2009!

Cheers!

SQL Objects vs. PL/SQL Tables

So I was writing a small procedure to loop through a procedure's arguments (all_arguments).

After getting it working, I then began to port it to packaged code, which is where I ran into a little problem.

Here's the initial statement:

DECLARE
  TYPE r_records IS RECORD
  (
    owner VARCHAR2(30),
    package_name VARCHAR2(30),
    procedure_name VARCHAR2(30),
    overload VARCHAR2(40),
    argument_name VARCHAR2(30),
    sequence INTEGER,
    in_out VARCHAR2(9)
  );
  TYPE t_records IS TABLE OF R_RECORDS INDEX BY BINARY_INTEGER;
  l_records T_RECORDS;
BEGIN
  SELECT
    owner, 
    package_name, 
    object_name,
    overload,
    argument_name,
    sequence, 
    in_out 
  BULK COLLECT INTO l_records
  FROM dba_arguments
  WHERE package_name = 'DBMS_UTILITY'
    AND object_name IN ( 'COMPILE_SCHEMA', 'INVALIDATE' )
  ORDER BY owner, package_name, object_name, position;
END;
/

Runs fine.

My first step to refactor was to use the TYPE declaration in the package header. But you can't do that (little rusty on pl/sql tables). So I created SQL Objects or User Defined Types (UDT).

CREATE TYPE r_procedure_arguments AS OBJECT
(
  owner VARCHAR2(30),
  package_name VARCHAR2(30),
  procedure_name VARCHAR2(128),
  overload VARCHAR2(40),
  argument_name VARCHAR2(30),
  position INTEGER,
  sequence INTEGER,
  in_out VARCHAR2(9)
);
/
show errors

CREATE TYPE t_procedure_arguments AS TABLE OF R_PROCEDURE_ARGUMENTS;
/
show errors

Then I rewrote the anonymous block to use the UDT.

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out 
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /
  FROM all_arguments;
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

not enough values? OK, let's go through it. There are 8 "columns" in the defined object and 8 in the SELECT clause. Hmmm...I tried adding some extra values at the end:

DECLARE
  l_records T_PROCEDURE_ARGUMENTS;
BEGIN
  SELECT
    owner,
    package_name,
    object_name,
    overload,
    argument_name,
    position,
    sequence,
    in_out, 1, 2, 3, 4
  BULK COLLECT INTO l_records
  FROM all_arguments;
END;
/

Same result, not enough values.

I spent the next hour toggling between gmail, facebook and meebo thinking about the problem.

Wait! You have to "cast" the values from the SELECT clause!

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      R_PROCEDURE_ARGUMENTS( owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out )
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Duh.

So I post it here so next time I don't waste an hour trying to remember why I can't get it to work.

This is the LAST time...

On Friday I was furloughed...and today I was invited back. I start tomorrow.

Wow is really all I can say. Perhaps I should write a book or something? These past couple of years certainly qualify as book worthy. :)

The Tampa Timeline

I moved to Tampa a little over two years ago. To say it's been hectic would be a gross understatement. Here's the events myself and my family have been through in that short time:

November 27, 2006 - Begin work at WellCare as a contractor.
December 2, 2006 - Closed on Gainesville house
December 17, 2006 - Purchased Tampa house
December 26, 2006 - Rolled into a fulltime position at WellCare in the Datawarehouse group
January 4, 2007 - New CIO announced at WellCare
April 21, 2007 - Kate gives the Reaper the finger Summer 2007 - Awarded on-the-spot bonus, only to be *PIPped 2 days later
August 6, 2007 - Sent a "manifesto" to my CIO August 14, 2007 - Started this blog
October 24, 2007 - FBI Raids WellCare with 200 agents
January 26, 2008 - CEO and CFO resign from WellCare
April 24, 2008 - Gave my first professional presentation on Application Express
May 22, 2008 - I write a short statement on my blog that WellCare is doing layoffs
May 27, 2008 - I am fired from WellCare, with cause, for posting company "confidential" information
May 31, 2008 - I get my official offer from Revolution Money as a contractor June 15, 2008 - My paternal Grandfather passes away at 89
July 27, 2008 - I'm let go from Revolution Money
September 2, 2008 - I start as a fulltime employee at Revolution Money
December 26, 2008 - I'm furloughed from Revolution Money
December 29, 2008 - I'm asked to come back to Revolution Money.
April 8, 2009 - I resign and am then escorted from the premises.
April 10, 2009 - I start my new job.
October 16, 2009 - I get fired let go again.
November 3, 2009 - Became an Oracle ACE

What have I learned in this time?
1. Don't go into the office. If you boss says he needs a word, say you have to go to the bathroom and run for your car.
2. Leave early. If layoffs are coming and you aren't there, they can't let you go!
3. Don't check email while on vacation.
4. Life is rough sometimes, but you have to roll with it.
5. Have fun. We spend more time with those at work than we do with our families. Enjoy yourself while you are there. I got to play ping pong everyday (after 5 naturally) at Revolution Money with my Arch Nemesis. I won two times...out of a thousand games. But it was fun.

*Performance Improvement Plan

Looking...Again!

Not once, not twice, but three times in one year!

So if you know anyone who needs an ORACLENERD, forward them on!

Thanks.

Exception Handling WTF

In homage to Oracle WTF.

Trying to remove references for a table that no longer exists (a view was created in it's place), I found this little gem:

OPEN c FOR
  SELECT 'INVALID USER'
  FROM dual;

  RETURN c;

It was nested in an IF-END IF block.

Instead of just throwing the exception or return 0 (zero) rows, this was the preferred method.

Awesome!

DDL Triggers and Security

I've mentioned in the past that I don't particularly care for trigger. That's a shocker, I know, because Tom Kyte loves them so much (here, here, and here).

We're trying to move to a named account approach (for developers too) and the only way to do that is to grant system level privileges. That's not good.

I had read about DDL triggers in the past but wasn't sure exactly how they could be used. Well, let's just say you can do quite a lot. There are database level events and client level events. The database level events include: STARTUP, SHUTDOWN, DB_ROLE_CHANT and SERVERERROR. A few of the client level events include (BEFORE and AFTER): DDL, LOGOFF, LOGON, GRANT, DROP, CREATE, ALTER, etc.

Being a developer my entire career (with certain DBA skills), I had never quite wandered into this territory. I was rather impressed.

The three I thought would make the most sense were ALTER, DROP and CREATE.

If you have system level privileges my biggest concern would be CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE (though the latter is not one that is currently granted). I've used those 2 privileges to get the DBA role in the past.

Here's what I need to do:
1. Does the user have the DBA role?

SELECT grantee
INTO l_grantee
FROM dba_role_privs
WHERE grantee = ora_login_user
  AND granted_role = 'DBA';

If they do have the role, it exits right then.
If not,
2. Where is the user trying to create/drop/alter an object? Is it in an approved schema? Their own?
3. raise_application_error if it's not approved or not their own object

Easy enough:

CREATE OR REPLACE 
TRIGGER system.no_create_in_dba_schema
  BEFORE CREATE
  ON DATABASE
DECLARE
  l_user VARCHAR2(30) := ora_login_user;
  l_owner VARCHAR2(30) := ora_dict_obj_owner;
  l_object_name VARCHAR2(128) := ora_dict_obj_name;
  l_dba VARCHAR2(30);
  l_can_create BOOLEAN;
BEGIN
  --is DBA?
  SELECT grantee
  INTO l_dba
  FROM dba_role_privs
  WHERE granted_role = 'DBA'
    AND grantee = l_user;

EXCEPTION
  WHEN no_data_found THEN
    l_can_create := can_user_create_alter_drop
                      ( p_user => l_user,
                        p_owner => l_owner );

    IF l_can_create = FALSE THEN
      raise_application_error( -20001, 'cannot CREATE objects in ' || l_owner );
    END IF;
END no_create_in_dba_schema;
/
show errors

In preparation for this post I found two very similar articles by Arup Nanda and Tom Kyte. I think they've trained me well.

Application Developers vs. Database Developers: Part II

You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently:

Mr. M:

OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!

Hey seriously, what a tub of shit Oracle is. Where does this myth come from that it's such a great platform? Their client tools suck balls and it's generally just a pain in the ass to work with from a developer's point of view. But devs for some reason are under this impression that from thew server perspective it's rock solid and performant. Well, it may be solid, but it's a fucking turd. Our dba here - definitely an Oracle guy - has been tasked with looking into moving us onto an oss db. He basically refuses to even look at MySQL, stating that it's a mickey mouse worthless pile of shit (can't really argue with him there lol), so that basically leaves Postgres. So it's been a few weeks now, and he will basically admit now that Postgres completely waxes Oracle as far as performance goes. LOL We run RAC in production too. He's looking at failover, replication, blah blah blah now, we'll see what the verdict on that is. Oh, and Oracle AQ? That's a worthless pile of shit too. Why do they tack a fucking message queue that doesn't play nice with jms onto the fucking database, which is probably already overworked? Oh wait, that's right, they're in the business of selling per cpu licenses! Cocksuckers.

This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse.

As I did before, I'll just put the email here.

Me:

Agreed, their client tools aren't all the great. Which ones are you using?

I use SQL*Plus (naturally), SQL Developer and JDeveloper. The latter 2 tie in very nicely with Subversion. With JDeveloper, when I want to try out Java, it's got a pretty good suite of tools.

Oracle starting out catering to businesses, Microsoft started with the consumer. Oracle has made pretty good strides in making their software more usable while Microsoft has made theirs more scalable.

I haven't used AQ a whole lot and definitely not with Java. I do know that it's billed as JMS compliant.

Postgres has it's place and so does Oracle. It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that.

Mr. M:

"It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that."

NO!!! NO!!! NOOOOO!!!

I want to beat people like you who say this with a ball pean hammer. There are only a select few individuals on this earth who can write and read application logic written in SQL. AVOID THIS ANTI-PATTERN AT ALL COSTS! What is it with you f_cking database guys??? Why do you always want to stuff as much crap into the db as possible?

DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!

It's fine for querying and manipulating sets of data, in a relational database. But it is a worthless sack of shit for expressing application logic!

I'm having to dig through this f_cking abortion right now because some Oracle f_ckhead thought "you can write entire applications using it!" Blog that, mofo!

This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either.

Me:

goodness gracious.

"DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!"

disagree (naturally). It's incredibly easy to do, you just don't know how yet...and it seems even the Oracle professionals out there don't either.

I'll tell you this, the crazier the SQL or PL/SQL needed to derive and manipulate data the poorer the design. Start with a good design and it all becomes very simple.

Of course note the use of "naturally" in my lexicon. Thanks Jake.

Mr. M:

well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.

btw, do the db guys here suck? seriously. i'm not competent enough to judge. (to be fair, apparently that crap i sent you is fairly old stuff, but still....)

Me:

I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.

It's further propogated due to the needs you specified in our discussion that day at WellCare (i.e. performance). I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data.

The code that I did look at (first 1000 lines or so) isn't great.
1. With all those IF THEN ELSE statements it's telling me that there's probably a better way to store the data. Looks like they're missing an attribute that should be contained with a track.
2. using Object (PL/SQL) types to store data in the way they seem to be doing it is not the best way. Again, probably a design issue.
3. When you do something like this:

   UPDATE pb_album_metadata
      SET primary_digital_flag = 0
    WHERE album_id IN (SELECT b.album_id
                       FROM (SELECT a.album_id AS album_id,
                                MAX(a.album_id) OVER (PARTITION BY a.standard_upc) AS latest_album_id
                             FROM pb_album_metadata a
                             WHERE a.standard_upc = g_album_tab(1).standard_upc ) b
                       WHERE b.album_id <> b.latest_album_id )
         AND primary_digital_flag <> 0;

They should probably have considered end_date as an attribute of the album metadata. While analytic functions are pretty cool, they're more for analyzing (OLAP) and not OLTP environments.

That's for starters and without table definitions...

Me (again):

oh yeah...and PL/SQL is/was built on top of ADA, FYI.

Mr. M:

"I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."

Huh? What are we not understanding? What would be an example of a constraint that would force us to write less and better code?

Me:

CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.

Instead of having to check if a value is NULL when submitting it to the database, just submit and let the database throw an error, which you'll conveniently capture in your Exception block. Same goes for CHECK constraints, columnA should be either one of three values (test, test1, test3), you won't have to check that in your application code, just catch the exception. FKs insure good data (proper relationships).

A different perspective. If you are going to pay for it, use that damn thing. If you don't care, don't do it. But over the course of an "enterprisey" application, you'll end up writing more code and make maintenance that much more difficult (did I do it there?). Just those constraints will force you and the teams of application developers to put good data into the database.

You can still do it in the application of course (form validation is a good place)...

Mr. M:

Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.

Dude, even f_cksticks like redacted I think have a decent enough understanding of when and how to use db constraints. It's when you get into things like cursors or cost plans of subselects and anonymous tables (i think that's the name for it - where I say select something from (select some other crap). Then we defer to db gurus like yourself.

But dude....."you won't have to check that in your application code, just catch the exception".......uh, don't ever repeat that off our little email group. And definitely don't go posting that on your blog. F_ck me man, it's a damn good thing we keep you db folks caged up in that rdbms box....

Me:

So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?

Why is it bad to deal with exceptions rather than coding to avoid them?

I highly doubt even redacted understood database constraints...him and his cohorts believed all database software would be commoditized and MySQL would be king in short order.

Mr. M:

"for a high transaction system"

Or for any system really....

To use your example of check constraints (is this value one of....) or not null constraints, checking these rules in the Java code and in the database code would seem to violate DRY. But we do that alot, and it is acceptable in certain cases. For instance, we also probably violate DRY if we're checking this same rule say in Javascript on the front end. But we accept this tiny violation of DRY because it tends to make for a better user experience and as a performance gain too, for we avoid the round trip to the server. Now, what your advocating here is close to the same thing. You're basically saying, don't check for the not null constraint in Java code, just go ahead and hit the database, let the database throw back an exception to the Java code, presumably correct the problem, and then make another roundtrip to the database again. Dude, what are you thinking?!? This to say nothing of the fact that this also could be considered a violation of Fail Fast, and a violation of Defensive Programming - what happens if the dba forgot to add the not null constraint in production?

Dude, listen to this guy. For a "high transaction system" basically you want to treat the database, as much as you can, as just a dumb data holder. A f_cking dumpster, that you just throw sh_t into and pull shit back out, using no vendor-specific features if at all possible.

Again, for we've had this discussion, but even in everyday apps, not just on Wall Street, the database is the bottleneck. And the database is the hardest layer to scale. So given those facts, you should design your app to do as little work in the database as possible.

I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point).
Me:

i agree in any application that you want to minimize the number of round trips...

shocker...he's one of our architects. he's spot on in many instances, but...

database is the bottleneck because people don't know how to write SQL. I'll certainly concede the wall street applications (for the time being anyway), but the rest of us with what we do on a daily basis...Oracle will scale far beyond the demands they place. When that bottleneck shows up, 9 times out of 10 some dumb-ass c#/java guy thought he could write a better query than i. besides, what's the idiot doing anything but a simple join anyway? probably poor data model to start with...and we're right back where we started (sort of).

Mr. M:

"database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."

Dude, I'll grant you, people don't know how to write SQL, myself included. But that's not always why the database is the bottleneck. I think it's safe to say that's not even the majority of the reason. Yes, there are apps written by people who were just idiots, and they needlessly pummel the database, but that's definitely not the majority of scenarios. At my work the database is the bottleneck, and we run RAC in production. It's my understanding that even with RAC, there is a limit to how much you can scale that out. But any layer up from the database we are basically unlimited in how much we can scale that out. So it's very easy to stick another Apache box in front, or fire up another Weblogic server. But I can't do that with the database. We have 24 Weblogic servers for redacted. The database is the bottleneck. And we don't have shitty sql code in the app. In fact, we have very few hand-written queries anywhere in the app. Persisting something to a database is really a low-level concern that as an application developer I shouldn't even have to be bothered with, except for the rare corner cases where the persistence abstraction I'm using is just a little bit too generic to handle things effectively. And we don't use these ORMs because we don't know how to write sql. To effectively use an ORM tool requires a deeper understanding of sql and databases than simply being able to write solid SQL code. (Not saying Java devs who use ORMs know SQL better than a dba, just that it requires better sql skills than is required of a Java dev to simply write JDBC/SQL.) Now, before you try to tell me that my ORM library isn't able to write as efficient of sql code as a dba of your caliber, keep in mind that ORM tools are pretty advanced. They're able to intelligently do things like batch sql updates, and let you apply transactional semantics much easier than with raw jdbc/sql. But the overwhelming reason developers have so thoroughly adopted ORM is because Structured Query Language is such a nasty piece of shit for expressing application logic. SQL is a declarative, procedural language. It's totally unsuited for writing application logic! This, more than anything else, is why organizations and dev teams should seek to restrict what is handled within a relational database as much as possible - because the programming interface to it is a fucking ancient backward dying dinosaur.

Mr. V (note, not Mr. M):

My 2 canadian cents:
The polyglot approach "... use different languages for different domain ..."
Database was developed to manipulate data and should remain there.
General purpose language was developed to encapsulate logic and should remain in that domain.
You should not use DB to encapsulate business logic (in my opinion) no more than you would use HTML to create complex logic.

While Java, C#, Python, etc are described as General Purpose languages, they, too, are really domain-constrained. Their expressiveness are confined (and should be) to express low-level, tersed, explicit, verbose, and repetive logic (if that makes any sense). Languages such as these are more suitable for low-level abstraction on top of which, richer, more expressive languages can be built. We are seeing this now with the emergence of languages on the JVM (and arguably on the .Net's CLR).

I think SQL as we know will take a back seat and a smart developer somewhere will create a new domain-specific language that lives on the VM and will push the SQL expression out of the RDBMS and closer to the code that needs it. We are not there yet, but Microsfot is trying (see LINQ and all ORM papers). This is not to say that there won't be isntances where tuning in the SQL-native language won't be necessary. However, 80 to 90% of simple CRUD cases will be handled closer to the logic code that uses the data code.

Again, that's my 2 canadian cents... I could go on. But I have a meeting with redacted.

Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up.

Me:

Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.

Ultimately for me, it comes down to simplicity. I can write an application using PL/SQL that will scale very well for 90% of the solutions. Of course that doesn't include the "fancy" javascript/Ajax stuff needed for the UI. In my ever so humble opinion, this is a much better solution for a business in the long run.
1. You're more likely to change the middle tier than the database tier. Java, asp, Ruby, what's next?
2. Fewer moving parts means lower cost of ownership, even with the CPU costs. One person, one, was able to build and maintain a 350 page website. Be hardpressed to do that with the more expressive languages.

I think all of us are purists and very passionate about what we do. It's probably the main reason we get along. I thoroughly enjoy these conversations because it does force me to think...and that's always a good thing.

Mr. V:

Haaa chet.
You sorta made my point than diverged away. Building an app in all PL/SQL is dangerous. It's no safer than me building an app in all Java. I can build very limited app in all Java. The moment I need to interact with other domain (UI, data, low-level native code, etc), I have to switch to something that is closer to what I am trying to do. If I need to create UI, I will pick a ui-centric environment, when I need to talk to DB, I will pass connect to a db and send in my SQL, and so forth. I will use Java as the glue to bring it all togher.

In the end, we may be saying the same thing, but using different accent. O well.

And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing.

PL/SQL Collections: FIRST and LAST

I learned something new today while trying to debug a procedure that calls another procedure that calls another procedure (joy).

I kept getting an ORA-06052: PL/SQL: numeric or value error when I ran it. Initially I thought that the wrong value was being passed to the procedure. So I littered the code with DBMS_OUTPUT.PUT_LINE to find out where exactly it was failing when I noticed that it would stop just before looping through a collection.

So I put a:

DBMS_OUTPUT.PUT_LINE( 'collection count:  ' || l_collection.COUNT );

just before the loop. When I ran it, it showed a count of 0, hmmmm....

The loop used the FIRST/LAST syntax like this:

FOR i IN l_collection.FIRST..l_collection.LAST LOOP

I wonder if the fact that there aren't any elements have something to do with it?

So I rewrote it to use:

FOR i IN 1..l_collection.COUNT LOOP

Loaded it back into the database and voila! It worked.

I then had to let the original developer know so I wrote up a simple test case for it.

CREATE TABLE t
(
  id NUMBER,
  something_else VARCHAR2(20)
);

DECLARE
  TYPE myrecord IS TABLE OF T%ROWTYPE;
  l_collection MYRECORD;
BEGIN
  SELECT *
  BULK COLLECT INTO l_collection
  FROM t;

  FOR i IN l_collection.FIRST..l_collection.LAST LOOP
    NULL;
  END LOOP;
END;
/

Run it and you get the error:

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 9

Changed it to 1..COUNT

  1  DECLARE
  2    TYPE myrecord IS TABLE OF T%ROWTYPE;
  3    l_collection MYRECORD;
  4  BEGIN
  5    SELECT *
  6    BULK COLLECT INTO l_collection
  7    FROM t;
  8    FOR i IN 1..l_collection.COUNT LOOP
  9      NULL;
 10    END LOOP;
 11* END;
 12  /

PL/SQL procedure successfully completed.

Index those Foreign Keys

I've been reading about this phenomenon for years over on asktom, but I had never actually encountered the problem, until today that is.

I'm helping out doing a small piece on another project. Trying to get back into the habit of "good" unit-testing, I have created some test data. One build script that runs the whole thing, a few user scripts and finally a teardown script that deletes all the data I have created.

Naturally, I run it via SQL*Plus; turning feedback and echo off and using a liberal number of PROMPT directives (very similar to how the ApEx install goes coincidentally). This is what my teardown script reports:

...deleting from child table 1
...deleting from child table 2
...deleting from child table 3
...etc, etc

Nothing fancy. Then I realized on the final DELETE it was hanging up. Any locked objects? Nope. Maybe it's the trigger? I checked, only saw ON INSERT OR UPDATE, confirmed that with another developer. He suggested unindexed foreign keys. Huh? Really? That's impossible you say.

It wasn't impossible. It was true. So I ran my copy of the Tom's "find unindexed foreign keys."

Quite a few "****" which is not good.

So I went searching for them myself and came up with this little query (for my specifc table):

SELECT 
  b.table_owner,
  b.table_name,
  b.constraint_name,
  b.column_name,
  a.index_name
FROM
  dba_ind_columns a,
  (
    SELECT 
      a.owner table_owner,
      a.table_name, 
      a.constraint_name, 
      b.column_name
    FROM 
      dba_constraints a,
      dba_cons_columns b
    WHERE a.constraint_name = b.constraint_name
      AND a.owner = b.owner
      AND a.table_name = b.table_name
      AND a.r_constraint_name = ( SELECT constraint_name
                                FROM DBA_CONSTRAINTS
                                WHERE TABLE_NAME = 'MY_TABLE'
                                  AND owner = 'SCHEMA_NAME'
                                  AND constraint_type = 'P' )
  ) b
WHERE b.table_owner = a.table_owner (+)
  AND b.column_name = a.column_name (+)
  AND b.table_name = a.table_name (+)
ORDER BY 
  b.table_name, 
  b.constraint_name

That gave me a list of all the columns referencing the primary key (some 37 different tables). If index_name was NULL, then I knew I had to add one. Since I have this obsession lately with generating code, I just used this query and added the following:

  ( CASE
      WHEN a.index_name IS NULL THEN
        'CREATE INDEX schema_name.' || b.table_name || '_' || b.column_name || '_idx
        ON ' || b.table_owner || '.' || b.table_name || '( ' || b.column_name || ' )
        TABLESPACE my_tablespace;' 
    END ) sql_text

Now I have my CREATE INDEX statements and all is well.

I run the teardown script again and it finishes like it should.