4 Replies Latest reply: Dec 30, 2012 4:02 AM by Stew Ashton RSS

    FUNCTION/TRIGGER AND COMMIT

    Oracle Maniac
      Hello friends ,



      When I relate of as to why we cannot use ddl/(dml with commit), inside a function then the below points came to my mind.

      1) Since often functions are like black box to the invoker ,so if one uses a function call right in the middle of his transaction ,then the DDl/(DML with commit) can prove to be very wrong since functions can be called from within a sql statements ,which can commit a part of the transaction without the invoker knowing of it . so the alternative comes to make it an autonomous transaction and I fully agree to this .


      2)But why isnt this check valid for a procedure .What i mean to say is that a procedure can be called from within some other plsql block .So why cant the above points be valid for procedures as well. I know it is not like that , but why ??


      Thanks in advance and I really appreciate everyone for their reply :)
        • 1. Re: FUNCTION/TRIGGER AND COMMIT
          jeneesh
          Rahul  K wrote:
          1) Since often functions are like black box to the invoker
          Never it is a black box..And you as a developer should not use any function in SQL (or anything in any code) without knowing what it does...And there is no difference between function and procedure in this regard..
          ,so if one uses a function call right in the middle of his transaction ,then the DDl/(DML with commit) can prove to be very wrong since functions can be called from within a sql statements ,
          It is not like that. SELECT statements are only for selecting and to analyze the data without changing the base data..You should never be changing the base data during SELECTion..
          which can commit a part of the transaction without the invoker knowing of it . so the alternative comes to make it an autonomous transaction and I fully agree to this .
          I have never used this in my career, just for using a function in a SELECT statements - still this is the way to do it..This PRAGMA is normally useful for logging and committing only the log entries using a triger (If required..)
          >
          >
          2)But why isnt this check valid for a procedure .What i mean to say is that a procedure can be called from within some other plsql block .So why cant the above points be valid for procedures as well.
          For function also this is valid, if you are using it in a PL/SQL block..You can use commit inside a function and can be called in a PL/SQL program..
          • 2. Re: FUNCTION/TRIGGER AND COMMIT
            Oracle Maniac
            Hi jeenesh ,

            The points still unclear to me:


            1)select statements are only for selecting , I agree ,but select can often be a part of a transaction ( if used in a sub query ) . and if i am wrong ,then why cant we use a commit/ddl inside a function ?Oracle must have thought of something before making this rule .



            2)Yes , i agree to your second point , inside plsql block functions can have commit without being Autonomous .
            • 3. Re: FUNCTION/TRIGGER AND COMMIT
              jeneesh
              Rahul  K wrote:
              1)select statements are only for selecting , I agree ,but select can often be a part of a transaction ( if used in a sub query ) .
              ??
              and if i am wrong ,then why cant we use a commit/ddl inside a function ?Oracle must have thought of something before making this rule .
              As I already told, you can use COMMIT in a function..

              And why not DDL?

              Read this from Great Billy

              >
              The basic reason is that PL/SQL code is compiled and static code.

              The language allows you to issue instructions to Oracle. This is converted into machine code or p-code (binary) instructions.

              Part of this compilation process is resolving references. Oracle does not refer internally to an object by name. That will be too slow. It refers to it by its unique number. So if you refer to object FOO, the compiler will resolve FOO and determine just who/where/what FOO is.

              Let's say you code the following in PL/SQL:



              begin

              CREATE TABLE emp ( empid NUMBER, surname VARCHAR2(20) );

              INSERT INTO emp( id, name ) VALUES( 1, 'Smith' );

              end;


              Hmm.. how can the compiler process this? It needs to create a reference for the object EMP for the CREATE TABLE statement, but EMP does not exist.

              Okay, let's assume the compiler should play dirty and do the CREATE TABLE anyway. Now it runs into a problem. I made a mistake with the column names in the INSERT. The EMP table does not exist. So how is the compiler suppose to create the p-code/machine code for this?

              The only way it can treat this is dynamically. Which is exactly what the EXECUTE IMMEDIATE statement does.
              >

              Also, read it atAsktom
              • 4. Re: FUNCTION/TRIGGER AND COMMIT
                Stew Ashton
                Rahul  K wrote:
                ... why we cannot use ddl/(dml with commit), inside a function ...
                You can do DDL, DML, COMMIT and ROLLBACK inside a function!
                SQL> create or replace function f return number is
                begin
                commit;
                return 1;
                end f;
                /
                FUNCTION F compiled
                
                SQL> declare
                l_num number;
                begin
                l_num := f();
                end;
                anonymous block completed
                As long as you call the function from PL/SQL, it is OK. The restriction is limited to calling the function from SQL. Read the part in red below:
                SQL> select f() from dual;
                
                Error starting at line 14 in command:
                select f() from dual
                Error report:
                SQL Error: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 
                ORA-06512: at "STEW.F", line 3
                14552. 00000 -  "cannot perform a DDL, commit or rollback inside a query or DML "
                *Cause:    DDL operations like creation tables, views etc. and transaction
                           control statements such as commit/rollback cannot be performed
                           inside a query or a DML statement.
                *Action:   Ensure that the offending operation is not performed or
                           use autonomous transactions to perform the operation within
                           the query/DML operation.
                This restriction is not limited to functions. It would apply to procedures as well, except that SQL statements cannot "call" procedures in the first place.

                The reason for this restriction is, in my view, statement-level atomicity. Oracle promises that a statement will either fully succeed or completely fail; if an exception occurs during statement execution, the entire statement is rolled back. This would be impossible if a function caused a commit or rollback in the middle of the statement.

                It would also be a debugging nightmare.