Home » SQL & PL/SQL » SQL & PL/SQL » Committing Transaction without handling exception in Procedure (Oracle 9i)
icon9.gif  Committing Transaction without handling exception in Procedure [message #425481] Fri, 09 October 2009 05:23 Go to next message
sarang_deshpande
Messages: 3
Registered: October 2009
Location: Pune
Junior Member

Dear All,
I have one scenario in procedure. 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.
Waiting for your replies.
Thanks and Regards.
Re: Committing Transaction without handling exception in Procedure [message #425482 is a reply to message #425481] Fri, 09 October 2009 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Also I can't write exception block for handling exception.

Why?

Post an example of what you are trying to do. It is not clear.

Regards
Michel
Re: Committing Transaction without handling exception in Procedure [message #425490 is a reply to message #425482] Fri, 09 October 2009 05:48 Go to previous messageGo to next message
sarang_deshpande
Messages: 3
Registered: October 2009
Location: Pune
Junior Member

This question was asked to me in one of the interview
For ex Consider following code

CREATE OR REPLACE procedure my_proc()
IS
BEGIN
INSERT INTO emp_temp(emp_no,job_cd)
SELECT emp_no,job_cd
FROM emp_job_history
WHERE update_dt > '29-MAR-2009';

INSERT INTO emp_salary_history(emp_no,salary)
SELECT emp_no,salary
FROM emp_mast
WHERE effective_dt > SYSDATE;
END;

In the above procedure I need to commit second statement without handling exception thrown by first statement.
Can I do this?
Re: Committing Transaction without handling exception in Procedure [message #425496 is a reply to message #425490] Fri, 09 October 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
If the first statement raise an exception the second one is never executed.
The only way is to handle the exception raised by the first statement.

Regards
Michel
Re: Committing Transaction without handling exception in Procedure [message #425516 is a reply to message #425490] Fri, 09 October 2009 06:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't keep us in the dark here.
Of course, when you didn't know the answer during the interview, you asked the interviewer what it was, right?
So he could see you are interested in learning new stuff plus because you simply wanted to know what you might have overlooked.
Re: Committing Transaction without handling exception in Procedure [message #425530 is a reply to message #425490] Fri, 09 October 2009 08:00 Go to previous messageGo to next message
joy_division
Messages: 4641
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 Go to previous messageGo to next message
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 #425561 is a reply to message #425556] Fri, 09 October 2009 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The requirement is "no exception handler".
Quote:
I need to commit second statement without handling exception thrown by first statement.


Regards
Michel
Re: Committing Transaction without handling exception in Procedure [message #425577 is a reply to message #425561] Fri, 09 October 2009 13:02 Go to previous messageGo to next message
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.
Re: Committing Transaction without handling exception in Procedure [message #425581 is a reply to message #425481] Fri, 09 October 2009 15:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
begin
   goto step5;
   insert1...
   insert2...
   insert3...
   insert4...

<<step5>> null;
   insert5...
end;
/


Guaranteed to make statement#5 without raising an exception on steps 1-4.

Makes no sense to me but satisfies your requirement.

Kevin
Re: Committing Transaction without handling exception in Procedure [message #425603 is a reply to message #425581] Sat, 10 October 2009 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thumbs Up Good one.

Regards
Michel
icon7.gif  Re: Committing Transaction without handling exception in Procedure [message #426083 is a reply to message #425603] Wed, 14 October 2009 01:13 Go to previous messageGo to next message
sarang_deshpande
Messages: 3
Registered: October 2009
Location: Pune
Junior Member

Thanks a lot to all the orafaq members for their valuable contribution.
Re: Committing Transaction without handling exception in Procedure [message #426126 is a reply to message #426083] Wed, 14 October 2009 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would have helped a lot if you could have been bothered to answer any of the questions directed at you.
Re: Committing Transaction without handling exception in Procedure [message #426233 is a reply to message #425481] Wed, 14 October 2009 09:02 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
not sure what everyone's problem is here.

OP, you were given the answer, "YOU CAN'T".

Kevin
Previous Topic: Synonyms
Next Topic: Receiving this error for a procedure call with plsql table as input pmtr
Goto Forum:
  


Current Time: Tue Dec 06 14:36:25 CST 2016

Total time taken to generate the page: 0.10543 seconds