Re: dbms_session.set_sql_trace
Date: 1998/03/19
Message-ID: <3511257d.23848360_at_www.sigov.si>#1/1
On Wed, 18 Mar 1998 17:46:43 -0600, edawad_at_yahoo.com wrote:
>Well Ian, I think this is caused by Oracle failing to assert the purity level
>of the procedure via the pragma restict_refrences statement, thus it can only
>be used in its parent package or from the command line. (Many useful Oracle
>provided functions and procedures have the same problem).
>
>To overcome this situation, you can add the pragma restict_refrences statement
>and recompile the package that contains the fuction or procedure you want to
>use (set_sql_trace):
>On the line immediately following the procedure declaration for set_sql_trace
>add:
> pragma restict_refrences(set_sql_trace,WNDS,RNDS,WNPS);
>
>to the package header file DBMSUTIL.SQL. When the package is recompiled, you
>should not have any problems.
>
>Please, let me know when it works.
Hm, I realy doubt that this pragma will solve the problem. What you described relates only to PL/SQL *functions*, wrapped in packages, called from *SQL statements* (it doesn't relate to procedures, nor to standalone functions, nor to any of them, if they are part of PLSQL expression).
It's obvious that error is related to user privileges. I'm almost certain that the owner of the procedure has ALTER SESSION privilege granted via role (very likely via CONNECT role, which has ALTER SESSION privilege granted by default). That is why the user can alter his session from SQL*Plus, but not from the PL/SQL stored object. The owner of the procedure must simply be granted ALTER SESSION explicitely (GRANT ALTER SESSION TO <user>).
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Mar 19 1998 - 00:00:00 CET