Answer / swapna

User_Errors table
OR
show error on sql promt.

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;

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.

Answer / shivashanker

select text from user_errors where name='PROCEDURE NAME OR FUNCTION NAME'

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

Describe different types of general function used in sql?

0 Answers  

explain the difference between myisam static and myisam dynamic. : Sql dba

0 Answers  

What has stored procedures in sql and how we can use it?

0 Answers  

Table 1: col1 Timestamp ---------------- 01-mar-2012 11:12:46 Table 2: col2 Timestamp -------------------- 01-mar-2012 11:12:10 01-mar-2012 11:11:23 Write a query to display a row with table2 col2 value less than tabl1 col1 value. Maximum timestamp value previous to table1 col1 value. Display a result as: Col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10

0 Answers  

Why stored procedure is faster than query?

0 Answers  

What is the difference between execution of triggers and stored procedures?

0 Answers  

How many types of literals are available in pl sql?

0 Answers  

what is error ora-03113: end-of-file on communication channel?

0 Answers  

Can you skip header records while loading? : aql loader

0 Answers  

What are the advantages of sql?

0 Answers  

package specification-3 procedures package body-4 procedures how can i write for other procedure in package body

2 Answers   Reliance,

What is a recursive stored procedure?

0 Answers