asked on

Oracle error handling

Guys

I have a very simple sql statement that when I run it the sql uses database links to get info from other databases.
The problem is some of these databases are TEST databases and may not be up so at the moment the statement errors and wont finish

My question is can I have a error handling to add for the line of script that cant run a message or just be blank but carry on and return data for what is available

select DBID,NAME,CREATED,LOG_MODE,OPEN_MODE from sys.v_$database
--union
--select DBID,NAME,CREATED,LOG_MODE,OPEN_MODE from sys.v_$database@IFSLEU
--union
--select DBID,NAME,CREATED,LOG_MODE,OPEN_MODE from sys.v_$database@PROD
--union
--select DBID,NAME,CREATED,LOG_MODE,OPEN_MODE from sys.v_$database@IFSL_TA
--union
--select DBID,NAME,CREATED,LOG_MODE,OPEN_MODE from sys.v_$database@IFSL_TA1

DarrenJackson

ASKER

sorry ignore the commented out parts I was testing

Alex [***Alex140181***]

Where is this statement supposed to run (shell script, java...)?!

DarrenJackson

ASKER

I want it to run in a crystal report. or if this is not possible to run as a scheduled job and update a table

then I will point my report to query it

Regards

Alex [***Alex140181***]

CR = I have no clue ;-)

If you want to use a "temp" table (being populated by a scheduled job), I'd write a stored proc, there exceptions can be trapped whenever you try to connect/select from a db which is down or whatever...

DarrenJackson

ASKER

That would be great

Can you give me script to create a stored procedure

Thanks

ASKER CERTIFIED SOLUTION

Alex [***Alex140181***]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

DarrenJackson

ASKER

Great I will test these out thanks

Alex [***Alex140181***]

@slightwv: good job, but imho hardcoding the db links (array/collection) is kind of bad practice (e.g. everytime a new db has to be checked, you'll have to change your code...)

DarrenJackson

ASKER

HI Guys

Thanks for the code I have checked over them both and I prefer to use the code from Alex140181 purely because I don't have to alter the code once applied.

I have 1 issue though the code runs fine but its random it will go through the links and sometimes miss out databases.

Is this because if the link doesn't return data in a timely fashion it ignores then moves on if this is the case can there be a WAIT clause added to give it time to respond with data??

any ideas

Regards

Alex [***Alex140181***]

this could be connected to this:

12535, 00000, "TNS:operation timed out"
// *Cause: The requested connection could not be completed within the timeout
// period specified by the CONNECT_TIMEOUT parameter in listener.ora. This
// error arises from the tnslsnr.
// *Action: Either reconfigure CONNECT_TIMEOUT to be 0, which means
// wait indefinitely, or reconfigure CONNECT_TIMEOUT to be some higher
// value. Or, if the timeout is unacceptably long, turn on tracing
// for further information.

Open in new window

http://dbaforums.org/oracle/index.php?showtopic=21451

Alex [***Alex140181***]

you might also try to TNSPING the remote databases (shell)....

DarrenJackson

ASKER

I have figured out the problem

I need to set my OPEN_LINKS parameter to greater than the current default of 4

Thanks guys for the help