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 ??

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..

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 .

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.
>

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.