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!
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?
ReplyDeleteDo 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.
'':-\
The problems were mutually exclusive, I obviously didn't articulate that very well.
ReplyDeleteA 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.