Home » SQL & PL/SQL » SQL & PL/SQL » oracle plsql...keyword that hints nothing should be done, and exit
oracle plsql...keyword that hints nothing should be done, and exit [message #253613] Tue, 24 July 2007 07:16 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member


CREATE TABLE test_tab (col1 VARCHAR2(30))





CREATE OR REPLACE PROCEDURE your_stored_procedure
    AS
    BEGIN
      BEGIN
   	 INSERT INTO test_tab VALUES ('1st statement');
      EXCEPTION WHEN OTHERS THEN NULL;
     END;
      BEGIN
       INSERT INTO test_tab VALUES (3/0);
        -- exit wont work here
     EXCEPTION WHEN OTHERS THEN NULL;
     END;
     BEGIN
       INSERT INTO test_tab VALUES ('3rd statement');
     EXCEPTION WHEN OTHERS THEN NULL;
     END;
   END your_stored_procedure;
   /




after the 2nd statement fails, i dont want the flow to
continue..and control should come out of the program

i tried exit;..but doesnt work

my program is bigger than this, and i want the control to
exit out of the program once the 2nd statement fails

can anyone please tell me what is the keyword to be used there?

instead of exit?
Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253622 is a reply to message #253613] Tue, 24 July 2007 07:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The keyword is removing the stupid exception handler
Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253624 is a reply to message #253613] Tue, 24 July 2007 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or better, remove the trigger, it is functionally equivalent to you current one.

Regards
Michel
Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253632 is a reply to message #253613] Tue, 24 July 2007 07:57 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member


scott@ORA92> CREATE OR REPLACE PROCEDURE your_stored_procedure
  2  AS
  3  BEGIN
  4    BEGIN
  5  	 INSERT INTO test_tab VALUES ('1st statement');
  6    EXCEPTION WHEN OTHERS THEN NULL;
  7    END;
  8    BEGIN
  9      INSERT INTO test_tab VALUES (3/0); -- will fail
 10    EXCEPTION WHEN OTHERS THEN NULL;
 11    END;
 12    BEGIN
 13      INSERT INTO test_tab VALUES ('3rd statement');
 14    EXCEPTION WHEN OTHERS THEN NULL;
 15    END;
 16  END your_stored_procedure;
 17  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXECUTE your_stored_procedure

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM test_tab
  2  /

COL1
------------------------------
1st statement
3rd statement



exception handler should be used as my function is bigger,
and there are several such blocks inside it...

so the idea is that if this statement fails..control should
go out of the program itself

Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253634 is a reply to message #253613] Tue, 24 July 2007 08:06 Go to previous messageGo to next message
resy
Messages: 86
Registered: December 2003
Member

Use exit after an IF condition
Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253638 is a reply to message #253613] Tue, 24 July 2007 08:18 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You might want to read about the RETURN statement

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems41.htm
Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253642 is a reply to message #253632] Tue, 24 July 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And think about what I said.

YOUR TRIGGER IS FUNCTIONNALY EQUIVALENT TO NO TRIGGER.

Stop working on it, it is a waste if time.

Regards
Michel
Re: oracle plsql...keyword that hints nothing should be done, and exit [message #253820 is a reply to message #253632] Tue, 24 July 2007 23:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
soujanya_srk wrote on Tue, 24 July 2007 14:57
exception handler should be used as my function is bigger,
and there are several such blocks inside it...

so the idea is that if this statement fails..control should
go out of the program itself


There cannot be a piece of code larger than one statement that needs a when others then null exception.
You have a bug.

If you want to exit your program when hitting an exception, you have to let that exception fall through to the procedures main exception handler, or (even better) to the calling program.
Only handle exceptions you know you want to handle, never use a when others then null.
(never = until you absolutely know what you're doing)
Previous Topic: Problem executing UNIX commands from PL/SQL
Next Topic: triggers
Goto Forum:
  


Current Time: Sat Dec 03 08:26:07 CST 2016

Total time taken to generate the page: 0.15553 seconds