asked on

oracle/psql exception details

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

Naveen Kumar

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

Naveen Kumar

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

karanba

ASKER

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.

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial