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 |
Delete duplicate records in the emp table.
What are sql commands?
Explain the update statement in sql
What is the difference between delete and truncate statement in sql?
Which one is the Best approach to put code, among Triggers and Procedures? Explain?
tell me about various levels of constraint. : Sql dba
one of the column in my table contains the data like SAL ---- 1000 1000 2000 3000 3000 So my requirement is i want output like SAL --- 1000 2000 3000 it mean i want to delete duplicate rows only how should u write query?
i have a table eno dno sal 1 10 200 2 10 150 3 10 100 4 20 75 5 20 100 i want to get sal which is less than the avg sal of thri dept. eno dno sal 2 10 150 3 10 100 4 20 75
Why do we use joins?
how to use in conditions? : Sql dba
How to generate a salary slip like jan 1000 1000 feb 1000 2000 ... dec 1000 12000
Why do we need cursor in pl sql?