How to trace the errors in pl/sql block code?
Answer Posted / 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 |
Post New Answer View All Answers
How do you remove duplicates without using distinct in sql?
What is sqlservr exe?
what is 'mysqlcheck'? : Sql dba
What is duration in sql profiler trace?
What are the advantages of pl sql over sql?
What are the types of subqueries?
how to include comments in sql statements? : Sql dba
What view means?
what is sql? : Sql dba
How do you rename a table in sql?
what is schema? : Sql dba
What are the types of views in sql?
What is time based sql injection?
What is clause?
How do you optimize a query?