Thanks for the question, Krastio.

Asked: June 28, 2019 - 6:30 pm UTC

Last updated: July 04, 2019 - 10:17 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

Hi,
The situation:
Procedure Master and procedure Slave, both include transactions.
Procedure Master calls procedure Slave in its body.
External code could calls both procedures.

The target:
When external code call procedure Slave the transaction should commit/rollback in the procedure.
When external code call procedure Master (and it calls procedure Slave) the procedure Slave should not commit but its transaction should be committed from main procedure Master.

Do you have any ideas how to detect the situation - is procedure Slave called directly or via procedure Master ?

Thanks,
Krastio Kostov

and Chris said...

I'd prefer not to commit at all in the slave procedure. And let master or external code commit when necessary.

But if you need to conditionally commit, you can add a parameter to decide whether you do:

create or replace procedure p ( 
  do_commit boolean default true 
) as
begin
  
  <stuff>
  
  if do_commit then
    commit;
  end if;
  
end p;
/

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

hide the implentation of the slave

Koen Lostrie, July 01, 2019 - 11:00 am UTC

If you don't like the additional argument, you could hide the implementation of the slave procedure and use a wrapper procedure that does a commit.

procedure slave_external
IS
BEGIN
  slave;
  COMMIT;
END;

Ensure that procedure slave is not callable externally by using the "ACCESSIBLE BY" clause it the definition.
That way external users cannot call slave directly but master can.

July 01, 2019 - 12:58 pm UTC

Nice idea Koen.

structural sol

Krastio Kostov, July 01, 2019 - 12:39 pm UTC

Thanks for the suggestion.
I think this is a very nice way, but this is legacy code and respectively - the generation of 1000 (about) new procedures is something that should be discussed with the client. Does Oracle have system tables/views/parameters to allow online situation identification?

July 01, 2019 - 12:59 pm UTC

Does Oracle have system tables/views/parameters to allow online situation identification

You've lost me - online situation identification of what?

just example

Krastio Kostov, July 01, 2019 - 6:40 pm UTC

my colleague gave me an idea - the lower expression will have a value of zero if we do not have a transaction at the moment and a positive value - if a transaction exists.
Unfortunately, this does not work if we do not have a transaction before calling the Slave procedure - which is a problem, because in that case the Slave procedure will not know it's called directly or via Master procedure.
This type of identification - is procedure called directly or not I name"online identification of the situation". :)
Sorry for long explanation.

Kind regards,
Krastio Kostov
================================
SELECT count(*)
FROM v$transaction t, v$session s, v$mystat m
WHERE t.ses_addr = s.saddr
AND s.sid = m.sid;

July 02, 2019 - 4:25 am UTC

True, but you need to be careful - all it takes a lock, eg

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
---------------------------------------------------------------------------------------


SQL> select * from scott.emp for update;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
---------------------------------------------------------------------------------------
27.0.5789

using OWA_UTIL.who_called_me

Rajeshwaran Jeyabal, July 03, 2019 - 2:36 pm UTC

....
The target:
When external code call procedure Slave the transaction should commit/rollback in the procedure.
When external code call procedure Master (and it calls procedure Slave) the procedure Slave should not commit but its transaction should be committed from main procedure Master.
....
How about using OWA_UTIL.Who_called_me function call - something like this:

demo@PDB1> create or replace procedure slave
  2  as
  3     l_owner varchar2(128);
  4     l_name varchar2(128);
  5     l_lineno number;
  6     l_caller_t varchar2(128);
  7  begin
  8     owa_util.who_called_me(l_owner, l_name,l_lineno,l_caller_t);
  9     dbms_output.put_line( ' owner =' ||l_owner);
 10     dbms_output.put_line( ' name =' ||l_name);
 11     dbms_output.put_line( ' lineno =' ||l_lineno);
 12     dbms_output.put_line( ' caller_t =' ||l_caller_t);
 13  end;
 14  /

Procedure created.

demo@PDB1> exec slave;
 owner =
 name =
 lineno =1
 caller_t =ANONYMOUS BLOCK

PL/SQL procedure successfully completed.

demo@PDB1> create or replace procedure master_proc
  2  as
  3  begin
  4     slave;
  5  end;
  6  /

Procedure created.

demo@PDB1> exec master_proc;
 owner =DEMO
 name =MASTER_PROC
 lineno =4
 caller_t =PROCEDURE

PL/SQL procedure successfully completed.

That will help us to identify who invoked the "Slave" procedure.
Now we can safely add "commit" to "Slave" procedure, based on who invoke the procedure - something like this.

demo@PDB1> create or replace procedure slave
  2  as
  3     l_owner varchar2(128);
  4     l_name varchar2(128);
  5     l_lineno number;
  6     l_caller_t varchar2(128);
  7  begin
  8     owa_util.who_called_me(l_owner, l_name,l_lineno,l_caller_t);
  9
 10     --do all your transactions here.
 11     if l_owner ='DEMO' and l_name ='MASTER_PROC' then
 12             null;
 13     else
 14             commit;
 15     end if;
 16  end;
 17  /

Procedure created.

July 04, 2019 - 10:17 am UTC

Not a huge fan...

If the procedures are in a package, OWA_Util only returns the package name. Not the subprogram:

create or replace package pkg as 
  procedure master;
  procedure slave;
end;
/

create or replace package body pkg as 
  procedure master as 
  begin
    slave ();
  end;
  
  procedure slave as
    l_owner varchar2(128);
    l_name varchar2(128);
    l_lineno number;
    l_caller_t varchar2(128);
  begin
     owa_util.who_called_me(l_owner, l_name,l_lineno,l_caller_t);
     dbms_output.put_line( ' owner =' ||l_owner);
     dbms_output.put_line( ' name =' ||l_name);
     dbms_output.put_line( ' lineno =' ||l_lineno);
     dbms_output.put_line( ' caller_t =' ||l_caller_t);
   end;
   
end;
/

exec pkg.master;

owner =CHRIS
 name =PKG.MASTER
 lineno =4
 caller_t =PACKAGE BODY

So this won't work if you're doing the good thing and using packages.

It's also a bit messy if you need to defer the commit when other procedures call it. You have to change the child procedure. But with a parameter or wrapper you don't.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

hide the implentation of the slave

structural sol

just example

using OWA_UTIL.who_called_me

More to Explore

PL/SQL demos

PL/SQL docs

Commit / rollback in nested procedures calls

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

hide the implentation of the slave

structural sol

just example

using OWA_UTIL.who_called_me

More to Explore

PL/SQL demos

PL/SQL docs