How to call a procedure with inserts and an out parameter?

I am working on a procedure to automate some inserts based on an IN parameter. I also want to know if the inserts fail or not, so I created an exception that returns me a ‘FAIL’ if any error occurs. I have the procedure completed, and I’m sure it will do the inserts if I just run the procedure, but I need to use this inside of another one, therefore I need to be able to access the status of the inserts. Usually I would select * into variable, but because I have inserts in my procedure I get errors about using inserts inside of a query. Please help me find a way to be able to call my procedure to know if they fail and act accordingly.

Here is a sample set of data and a sample procedure with similar functionality to mine:

create table datatable(val1 INT, val2 INT, val3 INT);

create procedure insert_datatable(n in int, status out varchar2) is
status varchar2 := 'good';
begin
for i in 1.. n loop
    insert into datatable(val1,val2,val3) values (1,2,n)
end loop
commit;
exception
    when others then
        rollback;
        status := 'fail'
        return status
end;

The above proc would then need to be called to 1. do the inserts and 2. tell me if they failed. I tried (without success):

select insert_datatable into insert_status from dual;

Answer

You don’t need to declare a status variable local to the procedure; its already declared as an OUT parameter. You also should leave the COMMIT and ROLLBACK statements to the block where you call the procedure; this allows multiple procedures to be called in one transaction and then can commit them all or roll them all back. You also don’t use RETURN statements in a procedure.

CREATE PROCEDURE insert_datatable(
  n in int,
  status out varchar2
)
IS
BEGIN
  FOR i IN 1 .. n LOOP
    insert into datatable(val1,val2,val3) values (1,2,n);
  END LOOP;
  status := 'good';
EXCEPTION
  WHEN others THEN
    status := 'fail';
END;
/

Then you can call it using PL/SQL (you can’t use procedures in a query, you’d need a function for that):

DECLARE
  p_status VARCHAR2(20);
BEGIN
  insert_datatable(
    n      => 3,
    status => p_status
  );
  DBMS_OUTPUT.PUT_LINE( p_status );
END;
/

Which outputs good and puts values into the table:

VAL1 | VAL2 | VAL3
---: | ---: | ---:
   1 |    2 |    3
   1 |    2 |    3
   1 |    2 |    3

db<>fiddle here