|
|
|
|
|
Re: Committing Transaction without handling exception in Procedure [message #425530 is a reply to message #425490] |
Fri, 09 October 2009 08:00 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sarang_deshpande wrote on Fri, 09 October 2009 06:48
INSERT INTO emp_temp(emp_no,job_cd)
SELECT emp_no,job_cd
FROM emp_job_history
WHERE update_dt > '29-MAR-2009';
You will never get past the first statement as it will give ORA-01843: not a valid month error.
If you properly used DATEs like they are supposed to in Oracle, I hope you are not thinking that the insert may produce an ORA-01403: no data found error.
|
|
|
Re: Committing Transaction without handling exception in Procedure [message #425556 is a reply to message #425530] |
Fri, 09 October 2009 11:37 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Each statement needs it's own exception handler.
CREATE OR REPLACE PROCEDURE my_proc
IS
v_soft_err number := 0;
BEGIN
DECLARE
--ORA-01843: not a valid month error.
bad_month EXCEPTION;
PRAGMA EXCEPTION_INIT (bad_month, -01843);
BEGIN
INSERT INTO emp_temp
(emp_no, job_cd)
SELECT emp_no, job_cd
FROM emp_job_history
WHERE update_dt > '29-M-2009';
EXCEPTION
WHEN bad_month
THEN
DBMS_OUTPUT.PUT_LINE ( 'Whoops - bad month, but dont stop...' );
-- log the error to some table or whatever
v_soft_err := v_soft_err + 1;
END;
INSERT INTO emp_salary_history
(emp_no, salary)
SELECT emp_no, salary
FROM emp_mast
WHERE effective_dt > SYSDATE;
DBMS_OUTPUT.PUT_LINE ( 'There were '||to_char(v_soft_err)||' soft errors...' );
END;
/
(untested code - but you get the idea)
[Updated on: Fri, 09 October 2009 11:37] Report message to a moderator
|
|
|
|
Re: Committing Transaction without handling exception in Procedure [message #425577 is a reply to message #425561] |
Fri, 09 October 2009 13:02 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Quote:My procedure contains multiple insert statements say 5. Now out of these 5 statements last insert statement needs to be executed. But the preceding 4 insert statement may or may not throw exception. Also I can't write exception block for handling exception.
So how can I commit last statement.
Reading between the lines, to me that means: Don't fail the proc on any of the first 4 statements because the 5th one still needs to run. That's why I illustrate how to suppress particular exceptions (or all) for the the ones preceeding the 5th one...
OP can clarify.
|
|
|
|
|
|
|
|