Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

oracle/psql exception details

Posted on 2007-07-25
4
Medium Priority
?
1,189 Views
Last Modified: 2013-12-19
In below oracle PSQL code I want to get more detail of exception. Like in which field and table, error related. So is there any object for error types.


----------------------------------------------------------------------------------------------------------
create or replace procedure SWAP_ALTAY(status out  varchar2)  is
begin
INSERT INTO edvs.onlineusers t (USER_NAME) values('aaajhfjkashdfkjashdfjkahskdjfhaskjdfhakjshdfkjashdfkjashdkfjhakj');

 exception when others then        
        status := sqlcode || '...' || SQLERRM || '...' || dbms_utility.format_call_stack;
         rollback;
end SWAP_ALTAY;
----------------------------------------------------------------------------------------------------------
output
-1401...ORA-01401: inserted value too large for column...----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c000000055de73e8         6  procedure EDVS.SWAP_ALTAY
c00000004f821160         5  anonymous block
0
Comment
Question by:karanba
  • 3
4 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19565947
may be we have to code our own error_routine for this.

create table error_Table
(table_name varchar2(30),stmt_slno number, err_message varchar2(4000), run_date date);

create or replace procedure SWAP_ALTAY(status out  varchar2)  is
stmt number:=0;
begin
stmt_nbr := 1;
INSERT INTO edvs.onlineusers t (USER_NAME) values('aaajhfjkashdfkjashdfjkahskdjfhaskjdfhakjshdfkjashdfkjashdkfjhakj');
stmt_nbr := 2;

 exception when others then        
        status := sqlcode || '...' || SQLERRM || '...' || dbms_utility.format_call_stack;
         rollback;
        insert into error_table(table_name,stmt_slno, err_message, run_date )
           values(upper('onlineusers'),stmt_nbr,status, sysdate);
end SWAP_ALTAY;
/

We have something of this sort in all our pl/sql code.

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19565969
forgot to mention: you can also add function/procedure name and which exception as well....

 status := 'SWAP_ALTAY-->others exception:' || sqlcode || '...' || SQLERRM || '...' || dbms_utility.format_call_stack;

Thanks
0
 

Author Comment

by:karanba
ID: 19567048
this is not the point of my problem. I want to more detail on error. But not to code that by hand.
Oracle try to insert and got smthg wrong, there throw an exception but where is the exception detail, like which value is tryed to inserte to which field? I am talking about an unknow table not a specific one.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1500 total points
ID: 19568006
i have not seen something of that sort. ok...i will also watch for updates on this question for other experts comments
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

577 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