Tuesday, December 23, 2008

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!

2 comments:

Mike said...

When you say you're trying to remove references to a table that no longer exists, and that a view had been created in its place: '':-\ (my favorite emoticon of late - me scratching my head...I find I can use that one ALOT)...um, what do you mean?

Do you mean that a view was created that referenced the table, and then the table was dropped, rendering the view invalid?

First.

Then second, "removing references to the table"...'':-\...do you mean that you want to find objects in the db that reference the non-existent table so you can modify/drop those as well?

If so, how does this cursor help you do that? Seems to me that "SELECT 'INVALID USER' FROM DUAL" would always return a row and would therefore not be a reliable indicator.

'':-\.

Thanks for your help.

'':-\

oraclenerd said...

The problems were mutually exclusive, I obviously didn't articulate that very well.

A table was deprecated, but a view was used to replace it so not to break everything...when I say everything, I mean there were over 700 references to this table.

The view replicated the functionality of the table mostly for JOIN purposes. Since it's a view the columns are un-indexed thus causing performance degredation.

As I was pouring through thousands of lines of code, I ran across the SELECT 'INVALID USER' FROM dual gem.