Re: alter session set sql_trace inside a procedure

Hello,

"FC" <flavio_at_tin.it> wrote in message
news:y522c.3026$O31.82972_at_news4.tin.it...
> 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
>

You have to grant the alter session privilege directly, granting via a role is not sufficient:

grant alter session to <user>

... or use AUTHID CURRENT_USER which is not advisable.

VC

>
Received on Fri Mar 05 2004 - 13:26:30 CST