Home » SQL & PL/SQL » SQL & PL/SQL » Not urgent (How to ignore a set error code?)
Not urgent (How to ignore a set error code?) [message #245804] Mon, 18 June 2007 18:40 Go to next message
Snoreboy01
Messages: 2
Registered: June 2007
Junior Member
Hi there I am snoreboy01 and this is my first post, how is it all going? Smile

Using oracle 9.2.0.7

This is not an urgent question as it isn't a problem right now but it is something we need to keep an eye on when we run it.

Is there a way to ignore a set error code with pl/sql?
The reason I ask is that we currently have a script that catches any sql error and breaks out so we know there is a problem. The sql we are running creates a table does some temp work with it and then drops it at the end, but we were thinking of adding in a drop table at the start in case for some reason the drop table at the end isn't run for some reason. (EG someone runs the script and cancels out of it leaving the table created and then someone latter runs the script again.)

The problem is that if it tries to drop the table and the table does not exit, then there is an sql error and it will drop out. (From my understanding of it.)

Is there a way to say if any sql errors occur except code *blah* then error out?

Thank you for your time.
Re: Not urgent (How to ignore a set error code?) [message #245806 is a reply to message #245804] Mon, 18 June 2007 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
As an alternative you should invesigate Global Temporary Tables (GTT)
Re: Not urgent (How to ignore a set error code?) [message #245817 is a reply to message #245806] Mon, 18 June 2007 21:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Fully agree with @anacedent - you should be using Global Temporary Tables.

However, should you choose to ignore this very good advice, you may ignore specific error messages using PRAGMA EXCEPTION_INIT and then handling the exception silently in the EXCEPTION clause.

Ross Leishman
Re: Not urgent (How to ignore a set error code?) [message #245819 is a reply to message #245804] Mon, 18 June 2007 21:54 Go to previous messageGo to next message
Snoreboy01
Messages: 2
Registered: June 2007
Junior Member
I like the global Temp Tables; the

[CREATE GLOBAL TEMPORARY TABLE my_temp_table
(
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;]

is really cool. Even if the session died it cleans it up... that is what I need. Smile
Thanks for pointing me to the right direction. Smile

Re: Not urgent (How to ignore a set error code?) [message #245835 is a reply to message #245819] Tue, 19 June 2007 00:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Good to see that you recognize the advice you got as useful.
Keep in mind that in Oracle, creation and dropping of objects is never done at runtime. This is something people coming from 'the other side' Wink (SQLServer/Sybase) have to get used to.
Re: Not urgent (How to ignore a set error code?) [message #245854 is a reply to message #245835] Tue, 19 June 2007 01:21 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I love the "not urgent" part in the title. Very Happy

MHE
Previous Topic: CONNECT BY PRIOR
Next Topic: DBMS_obfuscation package
Goto Forum:
  


Current Time: Mon Dec 05 15:13:09 CST 2016

Total time taken to generate the page: 0.05508 seconds