alter session set sql_trace inside a procedure

Hi all,
I know normally one issues alter session statements from sqlplus, but anyway, why is the following statement failing with Ora-01031 when run inside a pl/sql procedure?

...

Execute immediate 'ALTER SESSION SET SQL_TRACE = TRUE';
...

ORA-01031: insufficient privileges
ORA-06512: at "TEST.TEST_ALTER", line 6
ORA-06512: at line 1

Some addtional info:

Oracle 8.1.7 (both on Solaris and Windows machines). The procedure resides in a schema whose user is granted ALTER SESSION privilege.
The procedure runs with definer rights and is run by the owner. The same command succeeds when run "interactively" from sqlplus prompt. If I take just the "body" of the procedure and run it as anonymous pl/sql block, it works fine.

Here is the test procedure:

Procedure Test_Alter
is

   a number;
Begin

   Execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ' || chr(39) || 'test' || chr(39);

   Execute immediate 'ALTER SESSION SET SQL_TRACE = TRUE';

• dbms_utility.exec_ddl_statement('ALTER SESSION SET SQL_TRACE = TRUE'); Select count(*) into a from dual; -- dbms_utility.exec_ddl_statement('ALTER SESSION SET SQL_TRACE = FALSE'); Execute immediate 'ALTER SESSION SET SQL_TRACE = FALSE'; End;

I also tried using DBMS_UTILITY and funnily enough, it doesn't return any errors,
but after checking the trace file (enabled manually) it seems it doesn't execute the command, just parses it!

Bye,
Flavio Received on Fri Mar 05 2004 - 10:42:06 CST