SQL>
SQL> CREATE OR REPLACE PROCEDURE myraise (err_in IN INTEGER := SQLCODE,msg_in IN VARCHAR2 := SQLERRM)
2 IS
3 BEGIN
4 IF err_in BETWEEN -20999 AND -20000
5 THEN
6 RAISE_APPLICATION_ERROR (err_in, msg_in);
7 ELSIF err_in > 0 AND err_in NOT IN (1, 100)
8 THEN
9 RAISE_APPLICATION_ERROR (-20000, err_in ||'-'|| msg_in);
10 ELSIF err_in IN (100, -1403)
11 THEN
12 RAISE NO_DATA_FOUND;
13 ELSE
14 DECLARE
15 cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
16 returnValue PLS_INTEGER;
17 BEGIN
18 DBMS_SQL.PARSE (cur, 'DECLARE myexc EXCEPTION; PRAGMA EXCEPTION_INIT (myexc, ' || TO_CHAR (err_in) || ');' || 'BEGIN RAISE myexc; END;',DBMS_SQL.NATIVE);
19 returnValue := DBMS_SQL.EXECUTE (cur);
20 DBMS_SQL.CLOSE_CURSOR (cur);
21 END;
22 END IF;
23 END;
24 /
Procedure created.
SQL>