This content has been marked as final. Show 4 replies
1 person found this helpful
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..
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 .
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:
CREATE TABLE emp ( empid NUMBER, surname VARCHAR2(20) );
INSERT INTO emp( id, name ) VALUES( 1, 'Smith' );
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
1 person found this helpful
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.
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.