Solved

DB_LINK and end-of-file on communication channel

Posted on 1998-08-24
5
978 Views
Last Modified: 2012-05-05
I am working with two oracle 7.3.4. oracle databases.
The databases are connect to each other by database links.
I have some deamons set up on one database wich are processing some database database transactions on both of the databases.
The problem arises when the database which is accessed throug the database link has been shut down. If the database comes up again the deamon still complains about  end-of-file on communication channel ....

I did some exception handeling to solve the problem but from time to time the exeception handeling does not seems to work because the errors seem to migrate...
The exceptions i am currently handeling are the ORA-3113 end-of-file on communication channel and the ORA-2068 02068, 00000, "following severe error from %s%s"
// *Cause: A severe error (disconnect, fatal Oracle error) received from
//         the indicated database link.  See following error text.
// *Action: Contact the remote system administrator.

How can i capture all the errors that are generated from the database link without using WHEN OTHERS wich will capture all the errors which means the database link errors but also all other possible errors.

0
Comment
Question by:jcasteel
  • 3
5 Comments
 
LVL 1

Expert Comment

by:petevdb
Comment Utility
You can still use the WHEN OTHERS clause.
In the when clause you just put
IF errornumber = 3113
THEN
  reconnect
ELSE
  raise;
END IF;
The raise connect in the when clause
will reraise the error just captured.
There is a standard stored function in PL/SQL that returns
the Oracle error number but I'm not sure about the syntax
I believe it is ERRNO. Together with ERRTEXT you can reprogram
the whole error handling in Oracle.

Peter.
0
 
LVL 1

Author Comment

by:jcasteel
Comment Utility
I knew that, but it I cannot distinquish the DB_LINK errors from the other ones.

If I recieve a DB_link error I want to handle it different then a "normal" error. It's just not clear to me what kind of error messages a DB_LINK can generate.

I will give more information about how my code is setup...

I have a package that handles all the execeptions in my application a gives a central place for handeling the exceptions

Package        EXCEPTIONS
  IS
--
-- This Package header contains all the execeptions for the
-- query application

  Resource_busy     EXCEPTION;
  PRAGMA EXCEPTION_INIT ( Resource_busy , -54 );

  Link_down     EXCEPTION;
  PRAGMA EXCEPTION_INIT ( Link_down , -3113 );
  PRAGMA EXCEPTION_INIT ( Link_down , -2068 );

  NOT_NULL_VIOLATION EXCEPTION;
  PRAGMA EXCEPTION_INIT ( NOT_NULL_VIOLATION , -1400 );
  PRAGMA EXCEPTION_INIT ( NOT_NULL_VIOLATION , -1407 );

END; -- EXCEPTIONS

The code is using this package as the following

          BEGIN
                --  -------------------------------------------------
                -- some code has been deleted
               ---     -----------------------------------------------
          EXCEPTION
                WHEN exceptions.Resource_busy THEN
                    v_msg := 'LOCKING';
                WHEN exceptions.Link_Down THEN
                    v_msg := 'LINK';
                WHEN exceptions.Unique_constraint THEN
                    v_msg := 'UNIQUE_CONSTRAINT';
                WHEN OTHERS THEN
                    raise_application_error( -20000 , 'When Others error' );
          END;


0
 
LVL 5

Accepted Solution

by:
Mujeeb082598 earned 100 total points
Comment Utility
Hi :)

I do not know on which platform you are running your Oracle. If u are running on NT then their is help file called ORA.HLP which list all the oracle errors.

In the help when u click on find u can make a complete word list database. Then u enter link and it will display all the error messages that has link in it.

U can then choose the errors that u want to handle in your exception package.

Let me know if u do not have NT version of Oracle i will send u this help file.
0
 
LVL 1

Author Comment

by:jcasteel
Comment Utility
Please mail the help file to me because we are running on a UNIX box

Greetings

Jan Casteels
Email : Jan.Casteels@advalvas.be
0
 
LVL 1

Author Comment

by:jcasteel
Comment Utility
Thanks, I added some more specific exception handeling to my code.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now