Continuing the process after an EXCEPTION [message #361377] |
Wed, 26 November 2008 02:48  |
wmgonzalbo
Messages: 98 Registered: November 2008
|
Member |
|
|
Hi experts,
I have procedure (run by a job) that reads from a table (TABLE1) that executes the procedures (from the proc_name field of TABLE1) and query conditions etc.. If an error occured, exception will be implemented and an 'ERROR' message will flag the last_run_status field insted of 'DONE'..
My prob is that if i have 5 procedures stored in that table, and an error occured in procedure 3, procedures 4 and 5 will not be processed..
1 DONE
2 DONE
3 ERROR
4
5
instead of..
1 DONE
2 DONE
3 ERROR
4 DONE
5 DONE
Is there a way that if an exception occurs my process for the whole table will still continue?
Sample Code:
CREATE OR REPLACE PROCEDURE MyProc1 IS
TYPE cv_mycursor IS REF CURSOR;
cv_mycursor cv_cursor;
v_genEndDate DATE;
v_row TABLE1%ROWTYPE;
BEGIN
open cv_cursor 'SELECT proc_name FROM TABLE1 order by process_order'
loop
fetch cv_cursor into v_row.procname;
exit when cv_cursor%NOTFOUND;
IF ...
-- Process v_row.procname
ELSIF ...
-- Process v_row.procname
ELSE
-- Process v_row.procname
END IF;
end loop;
dbms_output.put_line ('All processes for this day are done...');
EXCEPTION WHEN OTHERS
THEN
dbms_output.put_line ('* ERROR Process_Order: '||v_row.process_order||' Proc_Name:'||
v_row.proc_name||' Message: '||SQLERRM||' '||SQLCODE);
SELECT SYSDATE INTO v_genEndDate FROM dual;
UPDATE TABLE1
set last_run_date = v_genEndDate,
last_run_status = 'ERROR'
where process_order=v_row.process_order AND proc_name=v_row.proc_name;
COMMIT;
END;
So far im thinking of using GOTO as a work around? But is seems absurd that i use GOTO to continue the looping.. It is also not possible with this test code, forced exception error...
set serveroutput ON;
DECLARE
str varchar2 (5 BYTE) := 'aa';
str2 varchar2 (5 BYTE);
BEGIN
dbms_output.put_line ('Start str: '||str);
select TO_NUMBER(str) into str2 from dual;
<<here>>
dbms_output.put_line ('End str:'||str2);
exception when others then
dbms_output.put_line ('Error');
goto here;
end;
ORA-06550: line 15, column 2:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'HERE'
ORA-06550: line 15, column 2:
PL/SQL: Statement ignored
Im using Oracle 10g edition.. Hope ive explained my inquiry enough..
Thanks,
Wilbert
[Updated on: Wed, 26 November 2008 03:02] by Moderator Report message to a moderator
|
|
|
|
|
Re: Continuing the process after an EXCEPTION [message #365074 is a reply to message #361377] |
Wed, 10 December 2008 19:47   |
wmgonzalbo
Messages: 98 Registered: November 2008
|
Member |
|
|
Hi again experts,
Done with the codes about a week ago and works quite well with the dynamic queries, im just wondering now, if I have coded it efficiently (in terms of my exception handling inquiry), if this is what Michel C. pointed out?
Though i included several exceptions, I still included the WHEN OTHERS exception, perhaps i need to include more specified exceptions..
CREATE OR REPLACE PROCEDURE MyProc1 IS
TYPE cv_mycursor IS REF CURSOR;
cv_mycursor cv_cursor;
v_genEndDate DATE;
ctr NUMBER :=0;
v_row TABLE1%ROWTYPE;
BEGIN
open cv_cursor 'SELECT proc_name FROM TABLE1 order by process_order'
loop
<<here3>>
fetch cv_cursor into v_row.procname;
exit when cv_cursor%NOTFOUND;
IF ...
BEGIN
-- Process v_row.procname, increment ctr
EXCEPTION
WHEN VALUE_ERROR then ...
goto here1;
WHEN INVALID_NUMBER then ...
goto here1;
WHEN OTHERS then ...
goto here1;
END;
END IF;
<<here1>>
IF ...
BEGIN
-- Process v_row.procname, increment ctr
EXCEPTION
WHEN VALUE_ERROR then ...
goto here2;
WHEN INVALID_NUMBER then ...
goto here2;
WHEN OTHERS then ...
goto here2;
END;
END IF;
<<here2>>
IF ...
BEGIN
-- Process v_row.procname, increment ctr
EXCEPTION
WHEN VALUE_ERROR then ...
goto here3;
WHEN INVALID_NUMBER then ...
goto here3;
WHEN OTHERS then ...
goto here3;
END;
END IF;
end loop;
dbms_output.put_line ('Records Processed for this day: '||ctr);
dbms_output.put_line ('All processes for this day are done...');
COMMIT;
END;
I also use SET SERVEROUTPUT ON always when running procedures or functions to display valuable end results for checking purposes..
I believe there's some way that it will be SET ON always? or some way to prompt/remind me of the end results other than displaying using dbms_output.put_line?
Any pointers or tips will do.. Would really like to learn alot from you experts, im just a newbie..
Thanks,
Wilbert
|
|
|
Re: Continuing the process after an EXCEPTION [message #365076 is a reply to message #361377] |
Wed, 10 December 2008 20:58   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | I believe there's some way that it will be SET ON always?
|
No, there is none; it is client's work and it is not possible to force it from server (where the stored procedure resides).
Quote: | or some way to prompt/remind me of the end results other than displaying using dbms_output.put_line?
|
Yes, function return value or OUT parameters are designed for doing this - pass the result(s) to the caller. You may also RAISE the exception after all the processing under required conditions.
|
|
|
|
|