How to trace the errors in pl/sql block code?
Answers were Sorted based on User's Feedback
Answer / shai
Follow best practice for exceptions:
Try using: (available from 10g)
1. dbms_utility.format_error_backtrace
2. dbms_utility.format_error_stack
Example:
declare
x varchar2(10);
begin
select 15445454545 into x from dual;
exception
when others then
dbms_output.put_line
(dbms_utility.format_error_backtrace || ' ' ||
dbms_utility.format_error_stack);
end;
Is This Answer Correct ? | 7 Yes | 0 No |
Answer / madhuri
SHOW ERROR will show the error code and message in a pl/sql
block.
If you know where exactly the error is then uses number of
dbms statements before/after each sql statement.
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / shivashanker
select text from user_errors where name='PROCEDURE NAME OR FUNCTION NAME'
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / elumalai d
CREATE TABLE question38 (empid NUMBER(10),ENAME VARCHAR2(200));
INSERT INTO question38 VALUES(100,'AAAAA');
INSERT INTO question38 VALUES(200,'BBBBB');
INSERT INTO question38 VALUES(300,'CCCCC');
INSERT INTO question38 VALUES(400,'DDDDD');
INSERT INTO question38 VALUES(500,'EEEEE');
INSERT INTO question38 VALUES(600,'AAAAABBBBBCCCCC');
COMMIT;
CREATE OR REPLACE PROCEDURE p1 (
p_empid IN NUMBER,
p_ename OUT VARCHAR2
)
AS
v_ename VARCHAR2(10);
BEGIN
SELECT ename INTO v_ename FROM question38 WHERE empid = p_empid;
p_ename := v_ename;
END;
/
CREATE OR REPLACE PROCEDURE p2 (p_empid IN NUMBER)
AS
v_ename VARCHAR2(100);
BEGIN
p1(p_empid,v_ename);
dbms_output.put_line(v_ename);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('using dbms_utility.format_error_backtrace.....');
dbms_output.put_line(dbms_utility.format_error_backtrace||'-'||sqlerrm);
END;
/
EXECUTE p2 (600);
DROP PROCEDURE P1;
DROP PROCEDURE P2;
DROP TABLE question38;
EXECUTE p2 (600);
DROP PROCEDURE P1;
DROP PROCEDURE P2;
DROP TABLE question38;
Final Result:-
==========
PROCEDURE P1 compiled
PROCEDURE P2 compiled
anonymous block completed
using dbms_utility.format_error_backtrace.....
ORA-06512: at "PRDMMIS.P1", line 8
ORA-06512: at "PRDMMIS.P2", line 5
-ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Is This Answer Correct ? | 0 Yes | 0 No |
Explain 3 basic parts of a trigger.
What is procedure and function?
what is self join and why is it required? : Sql dba
What are different types of keys?
What is meaning of <> in sql?
What is extent clause in table space?
What are expressions?
What is a subquery in sql?
What is offset and limit in sql?
Are stored procedures compiled?
select * from emp where sal in(select max(sal) from emp) here there is any limit for in operator how many values accpect ?
What does the hierarchical profiler does?