CODEWITHSUNDEEP
oracle-databaseexecution
user3311382
user3311382

Reputation: 41

Exit execution when error occurs PL/SQL

I would like to know, how can I exit the execution when an error occurs. In Microsoft SQL Server there is a RETURN clause, which does the work. But I would like to know similar functionality in Oracle. I am using Oracle Sql Developer. Here is the script I am using:

First block throws error due to Unique Key Violation, even though it throws error the execution goes to next block and executes the insert statement. I want to end the execution or exit at first block of code itself. Please help me to write the code.

First anonymous PL/SQL block:

set serveroutput on;

BEGIN 
  insert into test values(1);
  insert into test values(1);
  COMMIT;  

  dbms_output.put_line('PRINT SOMETHING 1'); 

EXCEPTION
   WHEN OTHERS THEN
     if sqlcode <> 0
     then
        dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
        RAISE; 
     end if;
     return;
END;
/

Second anonymous PL/SQL block:

set serveroutput on;

BEGIN 
  insert into test values(6);
  COMMIT;  

  dbms_output.put_line('PRINT SOMETHING'); 

EXCEPTION
   WHEN OTHERS THEN
     if sqlcode <> 0
     then
        dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
        RAISE; 
     end if;
     return;
 END;
/

Upvotes: 4

Views: 30144

Answers (5)

TonyK
TonyK

Reputation: 17

The EXIT command is only for use within a loop in PL/SQL. The EXIT command leaves the loop at that point. If you use the EXIT command outside a loop in PL/SQL the compiler throws an error.

The EXIT command in SQLPlus exits the SQLPlus session.

This is confusing, because they are two different Oracle products. SQL*Plus can run PL/SQL and the EXIT statement is a valid statement in both products, but with different contexts.

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

You can nest the blocks into a single 'program unit'. In this way an exception in the first block will stop the whole program unit from executing, rather than just being limited in scope to the first block.

set serveroutput on;

BEGIN
  BEGIN 
    insert into test values(1);
    insert into test values(1);
    COMMIT;  

    dbms_output.put_line('PRINT SOMETHING 1'); 

  EXCEPTION
     WHEN OTHERS THEN
       if sqlcode <> 0
       then
          dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
          RAISE; 
       end if;
       return;
  END;
  BEGIN 
    insert into test values(6);
    COMMIT;  

    dbms_output.put_line('PRINT SOMETHING'); 

  EXCEPTION
     WHEN OTHERS THEN
       if sqlcode <> 0
       then
          dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
          RAISE; 
       end if;
       return;
  END;
END;
/

Upvotes: 2

user3311382
user3311382

Reputation: 41

Thanks for your valuable comments. JoshL, i tried using EXIT but i am ending up with error. Please correct my code( I am new to PL/SQL). "WHENEVER SQLERROR EXIT" is good to use but my issue is that I use these sql scriptsd in InstallShield, so InstallShield installers does not recognize these statements and throws error.

set serveroutput on;

BEGIN 

insert into test values(1);
insert into test values(1);

COMMIT;  

  dbms_output.put_line('PRINT SOMETHING 1'); 

  EXCEPTION
    WHEN OTHERS THEN
    if sqlcode <> 0
    then
    dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
    RAISE; 
    exit;
    end if;

END;
/

Upvotes: 0

JoshL
JoshL

Reputation: 10988

You should be able to use "exit" - see the Oracle documentation here: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12023.htm

Note that this will end your SqlPlus session, but I don't know of another way of doing it aside from using a single block or stored procedure.

Another useful statement is:

WHENEVER SQLERROR EXIT SQL.SQLCODE

Oracle documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm

Upvotes: 1

Brian McGinity
Brian McGinity

Reputation: 5935

If you create a stored procedure, you have more control and can exit whenever you like with a return statement.

So create a stored proc:

create or replace procedure myProc as
begin
   dbms_ouput.put_line('i am here');
   return;
   dbms_ouput.put_line('and not here');
end;

Then in sqlplus or developer:

exec myProc();

Upvotes: 6

Related Questions