Home » SQL & PL/SQL » SQL & PL/SQL » Trap the error code (ORA-02019, ORA-06550, ORA-00942) (merged 2 threads)
Trap the error code (ORA-02019, ORA-06550, ORA-00942) (merged 2 threads) [message #312676] Wed, 09 April 2008 10:38 Go to next message
kanmani_karuppannan
Messages: 23
Registered: November 2006
Location: India
Junior Member

When i tried to execute the below query (with db link), its showing one error.

SQL> select po_header_id into a from po_headers_all@trn1
2 where rownum=1;
select po_header_id into a from po_headers_all@trn1
*
ERROR at line 1:
ORA-02019: connection description for remote database not found


I like to capture this error in pl-sql by using PRAGMA EXCEPTION_INIT. I used the below procedure but its showing some other error code (ORA-06550,ORA-00942).Anyhow i tried to capture the same error code but its not working fine. Please help me on this.


SQL> declare
2 a number;
3 resource_unavailable EXCEPTION;
4 PRAGMA EXCEPTION_INIT(resource_unavailable, -02019);
5 --PRAGMA EXCEPTION_INIT(resource_unavailable, -00942); -- both error code not able to trap
6 begin
7 select po_header_id into a from po_headers_all@trn1
8 where rownum=1;
9 dbms_output.put_line('test1');
10 exception
11 when resource_unavailable then
12 dbms_output.put_line('test2');
13 when others then
14 dbms_output.put_line('test');
15 end;
16 /
declare
*
ERROR at line 1:
ORA-06550: line 7, column 2:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored

Thanks,
Kanmani
Re: Trap the error code (ORA-02019,ORA-06550,ORA-00942) [message #312688 is a reply to message #312676] Wed, 09 April 2008 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Trap the error code (ORA-02019,ORA-06550,ORA-00942) [message #312775 is a reply to message #312676] Wed, 09 April 2008 13:47 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The ORA-00942 error occurs when it tries to compile the code, before it tries to run it, so you get that error before it can be trapped within the code. You can only create code for valid tables. If you have a valid table, create the code, then drop the table, then the code becomes invalid.
Previous Topic: Specify [INSERT INTO] to run at a specified time every day
Next Topic: Need part of a column (merged)
Goto Forum:
  


Current Time: Sat Dec 10 01:25:52 CST 2016

Total time taken to generate the page: 0.13682 seconds