Rahul K wrote: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..
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 ,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..
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..
Rahul K wrote:You can do DDL, DML, COMMIT and ROLLBACK inside a function!
... why we cannot use ddl/(dml with commit), inside a function ...
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> 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
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.
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.