There are things called "autonomous transactions" in Oracle - they are in general "evil" things - to be avoided (and this is a good example of why you should never use them in plsql called from sql).


You can see this in the following:

ops$tkyte%ORA11GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> insert into t values ( 2, 2 );

1 row created.

ops$tkyte%ORA11GR2> exec dbms_output.put_line( dbms_stats.create_extended_stats( user, 'T', '((x+y)/2)' ) );
SYS_STUSGY77M35GZYMH#M1HE#8VEY

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from t;

         X          Y
---------- ----------
         1          1

ops$tkyte%ORA11GR2> 

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select column_name, num_distinct, num_nulls, histogram
  2    from user_tab_col_statistics
  3   where table_name = 'T';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ---------------
X                                         1          0 NONE
Y                                         1          0 NONE
SYS_STUSGY77M35GZYMH#M1HE#8VEY            1          0 NONE

As you can see - the call to dbms_stats.create_extended__stats did not roll back - but the insert into t of (2,2) did rollback.

But, when we gather stats later, we can see the extended statistics "stuck", they were committed.

So, dbms_stats committed - but did not commit OUR transaction - it had its own recursive or autonomous transaction. That function therefore would be callable from SQL since it *apparently* doesn't do any DML or DDL or commits or anything - it is its own separate thing.


Now, why is this evil?

It is not restartable from within SQL. If you were to:

select dbms_stats.create_extended_stats( user, 'T', columns_to_create_them )
  from some_table;

and suppose that processed 10 rows out of 20 - how would you restart that??? It would want to reprocess the first 10 rows and would fail! Furthermore, if you ran that query in SQLPlus - you wouldn't even know how many rows SUCCEEDED! sqlplus doesn't have to print out the bulk fetch it does of a partial set of rows when it fails.


You want to call a function like this from a piece of procedural code, in a loop if necessary with proper error handling - so you can do things like catch errors and maybe ignore the "ora-20007 extension already exists" error.


http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/autotransaction_pragma.htm#LNPLS01302

I know of pretty much only one good use for autonomous transactions in your code - and that would be an error logging routine.