Oracle PL/SQL Tutorial/PL SQL Programming/Error — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Display error
<source lang="sql">
SQL> SQL> set serveroutput on SQL> SQL> create or replace procedure proc0 is
2 begin 3 RAISE no_data_found; 4 end; 5 /
Procedure created. SQL> SQL> SQL> create or replace procedure proc1 is
2 begin 3 NULL; 4 dbms_output.put_line ("calling proc0"); 5 proc0; 6 EXCEPTION 7 WHEN OTHERS THEN RAISE DUP_VAL_ON_INDEX; 8 end; 9 /
Procedure created. SQL> SQL> create or replace procedure proc2 is
2 begin 3 dbms_output.put_line ("calling proc1"); 4 proc1; 5 EXCEPTION 6 WHEN OTHERS THEN RAISE value_error; 7 end; 8 /
Procedure created. SQL> SQL> SQL> create or replace procedure proc3 is
2 begin 3 dbms_output.put_line ("calling proc2"); 4 proc2; 5 end; 6 /
Procedure created. SQL> SQL> SQL> BEGIN
2 proc3; 3 END; 4 /
calling proc2 calling proc1 calling proc0 BEGIN
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "sqle.PROC2", line 6 ORA-00001: unique constraint (.) violated ORA-06512: at "sqle.PROC1", line 7 ORA-01403: no data found ORA-06512: at "sqle.PROC3", line 4 ORA-06512: at line 2
SQL></source>
Get error information: SQLCODE, SQLERRM
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE getErrorInfo (
2 errcode OUT INTEGER, 3 errtext OUT VARCHAR2) 4 IS 5 c_keyword CONSTANT CHAR(23) := "java.sql.SQLException: "; 6 c_keyword_len CONSTANT PLS_INTEGER := 23; 7 v_keyword_loc PLS_INTEGER; 8 v_msg VARCHAR2(1000) := SQLERRM; 9 BEGIN 10 v_keyword_loc := INSTR (v_msg, c_keyword); 11 IF v_keyword_loc = 0 12 THEN 13 errcode := SQLCODE; 14 errtext := SQLERRM; 15 ELSE 16 errtext := SUBSTR ( 17 v_msg, v_keyword_loc + c_keyword_len); 18 errcode := 19 SUBSTR (errtext, 4, 6 /* ORA-NNNNN */); 20 END IF; 21 END; 22 /
SP2-0804: Procedure created with compilation warnings SQL> SQL> SQL> show error Errors for PROCEDURE GETERRORINFO: LINE/COL ERROR
-----------------------------------------------------------------
3/4 PLW-07203: parameter "ERRTEXT" may benefit from use of the NOCOPY
compiler hint
SQL> SQL></source>