Running multiple stored procedures and exit when there is error

Question

I have a stored procedure which runs a series of stored procedure. Each procedure will insert into a table which has the column status and Err_msg. So if I run the 1st procedure and the status shows 'Failed' with the error message, I want to exit the procedure completely. How do I do that?

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-12-04T09:54:37

To exit from a procedure in PL/SQL we use a simple RETURN statement.

If I have assumed correctly, field3 is an OUT parameter of each of your procedure called inside your main procedure.

CREATE OR REPLACE PROCEDURE sequence_test 
AS
field3 VARCHAR2(200) := 'Success';
BEGIN

MARS_TRUNCATE_TABLES('test','test',field3);
IF field3 like 'Failed%'  THEN -- use  = 'Failed' if it has only that.
  RETURN;  --exit the procedure completely.
END IF;

MARS_INSERT_TABLE('test','test',field3);
IF field3 like 'Failed%'  THEN 
  RETURN;  
END IF;

-- ...
-- ... Similar statements for other procedure calls.

END;
/

Running multiple stored procedures and exit when there is error

2 Answers