Re: dbms_session.set_sql_trace

[Quoted] 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.

In article <3510066C.F6BC9A8A_at_novalistech.com>,   Ian Wood <iwood_at_novalistech.com> wrote:
>
> hi,
>
> I have a stored procedure in which I want to invoke tracing. To this
> end I inserted the line:
>
> dbms_session.set_sql_trace(true);
>
> prior to the first executable line of code.
>
> when I run my test in SQL*Plus the following occurs:
>
> SQL> _at_test
>
> begin ns_val_sale.menu(-13, '000000003H'); end;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SESSION", line 32
> ORA-06512: at "SHELL_DBA.NS_VAL_SALE", line 2229
> ORA-06512: at line 1
>
> however, If I simply execute the procedure form the command line
> it works fine:
> SQL> execute dbms_session.set_sql_trace(true);
>
> PL/SQL procedure successfully completed.
>
> I would greatly appreciate it if someone could tell me what I am
> missing.
> Why can I execute from the command line and not from the script.
> DBMS_OUTPUT works fine by way of reference.
>
> Kindest Regards,
>
> Ian Wood
> NovaLIS Technologies
> iwood_at_novalistech.com
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 18 1998 - 00:00:00 CET