Discussion:
calling a stored procedure from a stored procedure
(too old to reply)
RU
2007-05-10 11:25:28 UTC
Permalink
Hi Folks,

I'm trying to define a (I thought) trivial stored procedure (SP) as
a front-end to the standard SP "dbms_system.set_ev",
which I want to use to generate a 10046 tracing event. I'm trying
to execute the following PL/SQL:

create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
dbms_system.set_ev(sid, serial, 10046, 4, '');
end;

and getting the error:

PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared

If I provide the schema in which "set_ev" is defined, in this case "SYS",
as in:

create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
sys.dbms_system.set_ev(sid, serial, 10046, 4, '');
end;

I get:

PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared


Any suggestions?

thanks,

RU
sybrandb
2007-05-10 11:45:29 UTC
Permalink
Post by RU
Hi Folks,
I'm trying to define a (I thought) trivial stored procedure (SP) as
a front-end to the standard SP "dbms_system.set_ev",
which I want to use to generate a 10046 tracing event. I'm trying
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
dbms_system.set_ev(sid, serial, 10046, 4, '');
end;
PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
If I provide the schema in which "set_ev" is defined, in this case "SYS",
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
sys.dbms_system.set_ev(sid, serial, 10046, 4, '');
end;
PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared
Any suggestions?
thanks,
RU
Look up PLS-201 in the Google archives. Your question has been asked
AND answered at least a million times before.

--
Sybrand Bakker
Senior Oracle DBA
Mark D Powell
2007-05-10 16:03:11 UTC
Permalink
Post by sybrandb
Post by RU
Hi Folks,
I'm trying to define a (I thought) trivial stored procedure (SP) as
a front-end to the standard SP "dbms_system.set_ev",
which I want to use to generate a 10046 tracing event. I'm trying
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
dbms_system.set_ev(sid, serial, 10046, 4, '');
end;
PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
If I provide the schema in which "set_ev" is defined, in this case "SYS",
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
sys.dbms_system.set_ev(sid, serial, 10046, 4, '');
end;
PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared
Any suggestions?
thanks,
RU
Look up PLS-201 in the Google archives. Your question has been asked
AND answered at least a million times before.
--
Sybrand Bakker
Senior Oracle DBA- Hide quoted text -
- Show quoted text -
There is an example cover package for dbms_system in the following web
article:

How do I switch on sql trace in another session that is already
running?
http://www.jlcomp.demon.co.uk/faq/alien_trace.html

HTH -- Mark D Powell --
DA Morgan
2007-05-10 15:58:26 UTC
Permalink
Post by RU
Hi Folks,
I'm trying to define a (I thought) trivial stored procedure (SP) as
a front-end to the standard SP "dbms_system.set_ev",
which I want to use to generate a 10046 tracing event. I'm trying
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
dbms_system.set_ev(sid, serial, 10046, 4, '');
end;
PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared
If I provide the schema in which "set_ev" is defined, in this case "SYS",
create or replace procedure rob_enable_tracing (
sid in integer,
serial in integer
)
is
begin
sys.dbms_system.set_ev(sid, serial, 10046, 4, '');
end;
PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared
Any suggestions?
thanks,
RU
You don't have explicit permissions granted to execute the package.
--
Daniel A. Morgan
University of Washington
***@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
RU
2007-05-12 02:07:01 UTC
Permalink
Post by DA Morgan
You don't have explicit permissions granted to execute the package.
spot on. thanks very much.

RU
Frank van Bortel
2007-05-12 10:02:12 UTC
Permalink
Post by RU
Post by DA Morgan
You don't have explicit permissions granted to execute the package.
spot on. thanks very much.
RU
The mantra is:
set role none
when developing stored procedures.

- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Loading...