Thanks for the question, Jason.
Asked: April 13, 2016 - 1:32 am UTC
Last updated: April 13, 2016 - 7:30 am UTC
Version: Oracle 11g
Viewed 1000+ times
You Asked
The source code that i'm using for test is at the bottom.
When the procedure runs to line1,(actually the result of clause 'select ztlzxh from ta_nsrlc where id=v_id' is null,so this clause will throw an exception),it runs to line2 ,then to line3 ,and jumps directly to the end of the procedure .What I have in mind is that it should go to line3 after the line1 being executed ,instead of running line2 ,because the line1 will throw an exception. Another puzzle is that when the code runs into exception,the 'INSERT INTO TA_QT_RUNLOG' clause should be executed to record the exception message ,but I haven't got any result by selecting the table TA_QT_RUNLOG after the procedure ended.
Could you help me explain it?
create or replace procedure CJ_TEST(V_ID CHAR, V_RETURN out VARCHAR2) is
V_DQZTLZXH CHAR(3);
V_ERRTEXT VARCHAR2(1000);
begin
line1: SELECT ZTLZXH INTO V_DQZTLZXH FROM TA_NSRLC WHERE ID=V_ID;
line2: V_RETURN := '1';
line3: EXCEPTION
WHEN OTHERS THEN
-- ROLLBACK;
--V_ERRTEXT := SUBSTR(SQLERRM, 1, 1000);
V_ERRTEXT := DBMS_UTILITY.format_error_backtrace||SUBSTR(SQLERRM, 1, 1000);
V_RETURN := '0';
INSERT INTO TA_QT_RUNLOG
SELECT SYS_GUID(),
V_ID,
'',
'',
'',
'CJ_TEST',
V_ERRTEXT,
V_RETURN,
SYSDATE
FROM DUAL;
end CJ_TEST;
and Connor said...
There is a difference between not finding a row, and finding a row that had a null value for the column. For example:
SQL> drop table TA_NSRLC purge;
Table dropped.
SQL> create table TA_NSRLC ( id int, ZTLZXH varchar2(10));
Table created.
SQL>
SQL> drop table TA_QT_RUNLOG purge;
Table dropped.
SQL> create table TA_QT_RUNLOG ( g varchar2(40), id int, tag varchar2(20), errm varchar2(4000), ret int, d date );
Table created.
SQL>
SQL> create or replace procedure CJ_TEST(V_ID CHAR, V_RETURN out VARCHAR2) is
2 V_DQZTLZXH CHAR(3);
3 V_ERRTEXT VARCHAR2(1000);
4 begin
5
6
7 SELECT ZTLZXH INTO V_DQZTLZXH FROM TA_NSRLC WHERE ID=V_ID;
8
9 V_RETURN := '1';
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 V_ERRTEXT := DBMS_UTILITY.format_error_backtrace||SUBSTR(SQLERRM, 1, 1000);
14 V_RETURN := '0';
15 INSERT INTO TA_QT_RUNLOG
16 SELECT SYS_GUID(),
17 V_ID,
18 'CJ_TEST',
19 V_ERRTEXT,
20 V_RETURN,
21 SYSDATE
22 FROM DUAL;
23 end CJ_TEST;
24 /
Procedure created.
SQL>
SQL> insert into TA_NSRLC values (1,'X');
1 row created.
SQL> insert into TA_NSRLC values (2,null);
1 row created.
SQL>
SQL> variable r varchar2(30)
SQL>
SQL> exec CJ_TEST(1,:r);
PL/SQL procedure successfully completed.
SQL> exec CJ_TEST(2,:r);
PL/SQL procedure successfully completed.
SQL> exec CJ_TEST(3,:r);
PL/SQL procedure successfully completed.
SQL> select * from TA_QT_RUNLOG;
G ID TAG
---------------------------------------- ---------- ----------------
ERRM
--------------------------------------------------------------------
RET D
---------- ---------
2A87B0D26DCC4C2D9F52829C8FE6C4ED 3 CJ_TEST
ORA-06512: at "MCDONAC.CJ_TEST", line 7
ORA-01403: no data found
0 13-APR-16
SQL>
btw, "when others" like that is a bad idea. Even if you capture some error information, make sure you re-raise back to the calling environment in that instance.
Rating
(1 rating)
We're not taking comments currently, so please try again later if you want to add a comment.