CODEWITHSUNDEEP
sqloracle-databaseplsqlprocedureoracle12c
4est
4est

Reputation: 3168

Checking execution of procedure (PL/SQL, Oracle)

I have below PL/SQL block:

Begin
   insert1();
   insert2();
   insert3();

   procedureTest();    
End

Each insertTest is making insert to some tables.

Now, if one of this procedure will not insert anything, my procedureTest should not execute.

How to do that?

Upvotes: 1

Views: 1257

Answers (4)

William Robertson
William Robertson

Reputation: 16001

One approach would be to have your insert1/2/3 procedures return the number of rows inserted as OUT parameters, which you could then capture and examine in the calling procedure.

declare
    l_rowcount1 integer := 0;
    l_rowcount2 integer := 0;
    l_rowcount3 integer := 0;

    l_rowcount_total integer := 0;

    procedure insert1
        ( p_rowcount out integer )
    is
    begin
        insert into sometable(a, b, c)
        select x, y, z from somewhere
        where  whatever = somethingelse;

        p_rowcount := sql%rowcount;
    end insert1;

begin
    insert1(l_rowcount1);
    insert1(l_rowcount2);
    insert1(l_rowcount3);

    l_rowcount_total := l_rowcount1 + l_rowcount2 + l_rowcount3;

    if l_rowcount_total > 0 then
        do_something();
    end if;
end;

Upvotes: 1

Alessandro Cucina
Alessandro Cucina

Reputation: 148

you have two possibilities to check if the three procedures insert rows:

1) launch a query or call a function to select the count of rows expected to be inserted after each call;

2) store the result of the command sql%rowcount: this command returns the count of rows affected from preceeding insert/update/delete instruction.

Note that, for the 2nd option, if insert1/2/3 insert rows using only one single insert instruction AND this single insert is the last statement of the procedure, then you can call sql%rowcount after the call to insert1/2/3 in the following way:

declare
   ret NUMBER := 0;
Begin
   insert1();
   ret := sql%rowcount; -- counts rows inserted in insert1

   insert2();
   ret := ret + sql%rowcount; -- counts rows inserted in insert1 + insert2

   insert3();
   ret := ret + sql%rowcount; -- counts rows inserted in insert1 + insert2 + insert3

   if ret > 0 then
      procedureTest();
   end if;
End

Instead, if the three procedures use more than one insert statement each OR perform a select or any other instruction after the insert, you have to check sql%rowcount inside insert1/2/3 immediately after each insert statement and return the sum of these results as output parameter (if insert1/2/3 are procedures) or as return value (if insert1/2/3 are functions).

Hope it helps.

Upvotes: 3

B. León
B. León

Reputation: 549

From this

if one of this procedure will not insert anything

I understand you want to stop execution of the rest of the code. So, for this, a suggestion would be: in those functions insert1/2/3, return the number of rows affected with:

sql%rowcount

Notice that this function counts the number of rows affected, so to speak, it counts only with insert, update, and delete statements (not select), for select, you'd need a cursor, more information you can find here: https://asktom.oracle.com/pls/apex/asktom.search?tag=rows-affected

Then, as those functions return a value that, if have inserted anything will return a number > 0, then you can store then in a variable and check their values, if any of then is == 0, then return, otherwise, execute procedureTest(). To interrupt execution, you can use return;

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

create or replace procedure insert1 is
  o_error varchar2(150);
begin
  -- your statement 1
 exception when others then rollback; o_error := ' an error occured! '; raise_application_error(-20101,o_error);
end;

Create procedures insert1 - 2 - 3 as preceding by iterating error code by one in every consecutive insert procedure as -20002 & -20003, and statements 2 & 3.

And call as you did before with a returning error argument:

Begin
   insert1;
   insert2;
   insert3;

   procedureTest();    
End

If any error raises, you'll see it as an alert and your program unit will stop running.

Upvotes: 0

Related Questions