how can use same transaction many function in PLSQL

Question

I'm very new for PLSQL. I need help a little bit. How to use same transaction for main function and sub function. My example if open comment line works but i would like to use one transaction in Main function. Is it possible?

CREATE OR REPLACE FUNCTION MAINFUNC(PAR1 IN NUMBER)
RETURN VARCHAR2
IS  
RITEM VARCHAR2 (1000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO table_name
       VALUES (value1,value2,value3,...);
RITEM  := SUBFUNCTION(PAR2);
COMMIT;
EXCEPTION
ROLLBACK; 
END;

subfunction here:

CREATE OR REPLACE FUNCTION SUBFUNCTION(PAR2 IN NUMBER)
    RETURN VARCHAR2
    IS  
    RITEM VARCHAR2 (1000);
    --PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    INSERT INTO table_name
           VALUES (value1,value2,value3,...);
    --COMMIT;
    EXCEPTION
    --ROLLBACK; 
    END;

What is the real question ? Your code use an unique (autonomous) transaction, and it is because you commented the commit and rollback in the subfunction, so it does exactly what you asked. — Massimo Petrus, Feb 13 '17 at 09:54
I want to use main function's transaction for sub function too but i couldnt and i get this error ORA-14552.But if i commited changes in sub function dont get any error @Massimo — Alper Tunga Arslan, Feb 13 '17 at 10:00
Why have you made the main function autonomous? That is very very rarely necessary or a good idea. — Alex Poole, Feb 13 '17 at 10:17
Actually,I m really new PLSQL. I research transaction and find it. Actually my real problem i couldnt use main function commit for subfunction. Is it possible? I want to finish only one commit end of main function. But throw ORA-14552 from sub function @AlexPoole — Alper Tunga Arslan, Feb 13 '17 at 10:27
The default behaviour is for everything in your session to use the same transaction - which exists until your session commits or rolls back. Making something autonomous breaks that. Unless you have a really good reason, remove that pragma. Committing in your sub function will commit all changes made in the session, by the main and sub. You should not really even commit form the main function - the caller should decide whether to commit or not. (What if one day you have another function that calls what is currently main?). These also look like they should probably be procedures. — Alex Poole, Feb 13 '17 at 10:41
thanks for your help @AlexPoole i convert to sub function to sub procedure and i handle it one commit all process. — Alper Tunga Arslan, Feb 14 '17 at 07:48

score 1 · Answer 1 · edited May 23 '17 at 12:01

There are very limited cases where you will want to put a COMMIT statement in a procedure or function and similarly there are fewer cases where you will make an AUTONOMOUS_TRANSACTION. For the majority of cases you will want to handle COMMIT and ROLLBACK statements in the transaction that calls the procedure/function.

If you want to call the function recursively then just include some terminating condition:

CREATE OR REPLACE FUNCTION MAINFUNC(
  PAR1  IN NUMBER,
  DEPTH IN NUMBER DEFAULT 0
)
RETURN VARCHAR2
IS  
  RITEM VARCHAR2 (1000);
BEGIN
  INSERT INTO table_name
       VALUES (value1,value2,value3,...);
  IF depth < 1 THEN
    RITEM  := MAINFUNC(PAR1, DEPTH + 1);
  ELSE
    RITEM  := 'Some Value';
  END IF;
END;

Then you can call it using:

DECLARE
  RITEM VARCHAR2(1000);
BEGIN
  RITEM := MAINFUNC( 42 );
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN -- Use a more specific error code here
    ROLLBACK
END;
/

how can use same transaction many function in PLSQL

1 Answers1