Solved

DB_LINK and end-of-file on communication channel

Posted on 1998-08-24
5
1,000 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 1

Expert Comment

by:petevdb
ID: 1081234
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
ID: 1081235
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
ID: 1081236
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
ID: 1081237
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
ID: 1081238
Thanks, I added some more specific exception handeling to my code.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

756 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