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 many tables can you join in sql?
What is program debugging?
Which type of cursor is used to execute the dml statement?
What is gpt format?
What is dml with example?
How to run sql*plus commands in sql developer?
Can we use threading in pl/sql?
Explain alias in sql?
What is a .db file?
Why we use join in sql?
What is trigger and types?
How much does sql cost?
How to run sql commands in sql*plus?
What is the maximum database size for sql express?
What is self-join and what is the requirement of self-join?