Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB_LINK and end-of-file on communication channel

Posted on 1998-08-24
5
Medium Priority
?
1,029 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 300 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

Independent Software Vendors: 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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