f



calling a stored procedure from a stored procedure

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
0
RU
5/10/2007 11:25:28 AM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

5 Replies
1052 Views

Similar Articles

[PageSpeed] 48

On May 10, 1:25 pm, RU <r...@vakuum.de> wrote:
> 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

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

0
sybrandb
5/10/2007 11:45:29 AM
RU wrote:
> 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

You don't have explicit permissions granted to execute the package.
-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
0
DA
5/10/2007 3:58:26 PM
On May 10, 7:45 am, sybrandb <sybra...@gmail.com> wrote:
> On May 10, 1:25 pm, RU <r...@vakuum.de> wrote:
>
>
>
>
>
> > 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
>
> 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 --

0
Mark
5/10/2007 4:03:11 PM
On Thu, 10 May 2007 08:58:26 -0700, DA Morgan wrote:
> You don't have explicit permissions granted to execute the package.

spot on. thanks very much.

RU
0
RU
5/12/2007 2:07:01 AM
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

RU schreef:
> On Thu, 10 May 2007 08:58:26 -0700, DA Morgan wrote:
>> 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...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGRZCkLw8L4IAs830RAizeAKCYMaXAAQgJ2EYMIzgAFk0IUD41LACeNyWk
J3zCo1OmUGgLu9DsAHfjWlA=
=EOE5
-----END PGP SIGNATURE-----
0
Frank
5/12/2007 10:02:12 AM
Reply:

Similar Artilces:

Calling a stored procedure from another stored procedure...
Goodmorning, I'm quite new to PostgreSQL, started off with version 8.0 some time ago. Recently I've begun working with stored procedures and now I've come accross a little problem / issue I can't seem to figure out. A quick overview of the situation: I have one stored procedure with returns a SET OF a new (record) type I've declared. For example: mytype index INTEGER; description VARCHAR; cost FLOAT; function calculate_cost(...): returns set of mytype; This stored procedure works without a problem. I can just do a &quo...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" ...

Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way for that reason). I would like to, say, execute a stored procedure in DB1, passing in the data blob and other details, DB1 will create a tree node in DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of course, as will DB2 when it adds the blob. Is this possible? [poste...

Executing Oracle stored procedure from a Java stored procedure
Hi, Here is my pb : Executing an Oracle stored procedure from a Java method included within a Java class stored on the Oracle database causes an error ORA-03113 "End of file on communication canal". When executing the Java method straight from a command line everything is ok. If executed from the Oracle server, I get this message. I need your help ! Alain Alain ROUILLON wrote: > Hi, > > Here is my pb : > > Executing an Oracle stored procedure from a Java method included within a > Java class stored on the Oracle database causes an e...

Oracle Stored Procedures VERSUS SQL Server Stored Procedures
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!! <jrefactors@hotmail.com> wrote in message news:1120793932.351921.5760@g43g2000cwa.googlegroups.com... > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? The concept shou...

Calling Stored Procedure remotely from other stored procedure (DB2 9.7 LUW)
Hello db2-experts, I'm planning an asynchronous application driven data replication. Therefor I want to call a stored procedure in database "x" on machine "b" from a stored procedure in database "y" on machine "a". Any ideas out there how to do it ? I found nothing appropriate in the manuals or examples. TIA Frank On Tuesday, September 4, 2012 8:37:33 PM UTC+2, Frank Mickler wrote: > Hello db2-experts, > > > > I'm planning an asynchronous application driven data replication. > > Therefor I want ...

Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get data for me while in another stored procedure. Its like a view, but a view you can't pass parameters to. In essence I would like a sproc that would be like this Create Procedure NewSproc AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... Or Delcare Table @TEMP @Temp = MAIN_SPROC 'a','b',..... Any ideas how I could return rows of data from a sproc into another sproc and then run a WHERE clause on that data? Thanks Chris Auer Instead of using the complicated stored proced...

Calling an Oracle PL/SQL procedure from MS SQL Server stored procedures
Hi, I'm totally new to both Oracle and MS SQL Server - so please forgive any vocabulary stupidities... The scenario is as follows: I want to transfer certain information from a database on a MS SQL Server to a Oracle server. This should happen once a day. I need the datatransfer to work in this manner: 1. A query is run on the SQL Server - resulting in X posts 2. Each post individually initiates a PL/SQL procedure located on the Oracle Server, that ensures data is submitted correctly. This procedure returns success true/false. The procedure needs various fields from the po...

Procedure in Stored procedure??
Hi all, Question from a guy who knows PL/SQL, but is starting with Transac-SQL on SQL Server.... Is it possible to have a sub procedure in a Stored Procedure, like we do in a PL/sql package ? Or better yet, have a kind of general Stored Procedure that would contain procedures called by more than one Stored Proc?? I can't find anything like this in the online books.... Thanks for your inputs.... Philip Hi SQL Server does not have packages and limited scope for shared variables. http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm http://tinyurl.com/n7v6 ht...

Stored procedure calling another stored procedure_
Hi all, I have a stored procedure that return a resultset e.g. stored proc: get_employee_details select emp_id, emp_name, emp_salary, emp_position from empoloyee I would like to write another stored procedure that executes the above stored procedure - returning the same number of records but it will only show 2 columns e.g. new stored proc: get_employee_pay -- executes get_employee_details I only need to know emp_id, emp_salary. How can this be done in sql stored procedure? Thanks, June Moore. June, I would make the first stored proc a UDF (user defined function) that ...

Why is it called Stored Procedure instead of Stored Sets?
Since RDMBS and its language SQL is set-based would it make more sense to call a given stored process "Stored Sets" instead of current theorically misleading Stored Procedure, as a measure to prod programmers to think along the line of sets instead of procedure? You are not storing a set; you are storing a procedure which hopefully works on a set. A stored procedure is a chunk of logic, not of data. I vote that the name is right. Not that your or mine opinion matters on this. MS and other vendors made their naming decision years ago and I cannot see any of the major RDBMS vendor...

DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main stored proc that may be calling multiple smaller stored procs? Just found out about this need late this afternoon and wondering what the best a...

Calling an Oracle stored procedure
Hello, I already succeed to call a stored procedure in Oracle but only simple ones with one output value and several inputs. I don't any idea left so any help would be great :) Thanks But on one procedure, i'm having a prb: I get an error: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too smalljava.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small In the product documentation, they don't give any information about the out/in parameters... They just describe: procedure creationEnteteBLC codeTiers VARCHAR2 client code codeDepot VARCHAR2 don't set it dateDocument DATE document date codeDocUtil VARCHAR2 code generated for the document codeDocInterne NUMBER Code generated for the lines, use it to create lines NUMBER indicate if all was fine... And this is my code cs =3D connOracle.prepareCall("{call ? :=3D APISOFT_DOS_" + dossier + "=2EGES_APPEL_EXT.CREATIONENTETEBLC(?,?,?,?,?)}"); cs.registerOutParameter(1, Types.INTEGER); cs.setString(2, "411AGUT01"); cs.setNull(3, Types.VARCHAR); Date date =3D new Date(2005, 1, 1); cs.setDate(4, date ); String codeDocUtil =3D""; cs.registerOutParameter(5, Types.VARCHAR); cs.setString(5, codeDocUtil ); int codeDocInterne =3D1; cs.registerOutParameter(6, Types.INTEGER); cs.setInt(6, codeDocInterne ); cs.execute(); And I get java.sql.SQLException: ORA-06502...

calling oracle stored procedures
i am pulling data from oracle tables where some fields in each table are encrypted, and the oracle dba has a stored procedure to decrypt these fields. how can i decrypt these fields while pulling them into sas? i have used this code and it works: proc sql; connect to oracle(user='xxxxxxxx' orapw='xxxxxxxx' path="xxxxxxxx"); create table test as select * from connection to oracle (select xxx_xxxx.encryption.decrypt(idnumber) from xxx_xxxx.tbl_ptdata); disconnect from oracle; quit; this works and will decrypt the field idnumber. i would like to see if there is a b...

Using stored procedure result set in another stored procedure
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will not work. I've been trying to get this to work with converting the UDF to a procedure, but I'm having no luck. Here is the background on what I'm trying to accomplish. I need to perform a sub-identity on a table, I have the normal identity set, but there are multiple duplicates in the table and I need each set of duplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup 2)...

calling a stored procedure in oracle
Hello, I need to call a stored procedure in a oracle database to retrieve some data. Does anyone have an idea how to do this in VO. Many thanks, Dirk Dirk, Do you use VO2Ado? Geoff "DirkPeeters" <dirk.peeters@clova.be> wrote in message news:dirk.peeters@clova.be: > Hello, > > I need to call a stored procedure in a oracle database to retrieve some > data. > Does anyone have an idea how to do this in VO. > > Many thanks, > > Dirk Hello Dirk, to call a procedure in Sybase I use a SqlStatement "call proc...

How to return a Pk value from one stored procedure to another stored procedure
Dear All, I have one stored procedure like sp_insertEmployee Employee Table Fileds Eno int pk, ename varchar(100), designation varchar In stored Procedure After inserting the ename and designation it has to return the Eno pk Id automatically I have another Department deptno int pk, Eno int fk, Depname varchar In this stored procedure I need to execute the sp_insertEmployee Stored procedure and we need that Pk return value after executing that By using that Id in this Department table we will insert the eno can u help me out on this issue Thanks CREATE PROCEDURE sp_i...

how to call a stored procedure from an oracle job ?
HI What i am trying to do is to call a stored procedure 'proc' defined for user 'user1' from an oracle job every midnight at 12. the sql snipp BEGIN DBMS_JOB.SUBMIT (job => 1 ,what => user1.proc ,next_date => 'TRUNC(SYSDATE + 1)' ,interval => 'TRUNC(SYSDATE + 1)' ); END; only says: ,what => user1.proc FEHLER in Zeile 4: ORA-06550: Zeile 4, Spalte 21: PLS-00222: In diesem Geltungsbereich ist keine Funktion mit dem Namen 'proc' vorhanden ORA-06550: Zeile 2, Spalte 1: PL/SQL: Statement ignored ...

Calling Oracle stored procedure in C#???
I'm trying to call a parameterized stored procedure in C# using microsoft ODBC .NET. Could you please give me some help or hint? Thank you very much! C# code: .... //connection code odbcCommand.CommandType = CommandType.StoredProcedure; odbcCommand.CommandText = "BEGIN myProc(?, ?); END;"; odbcCommand.CommandTimeout = 0; OdbcParameter paramA = odbcCommand.Parameters.Add("@A_CNT", OdbcType.Int); paramA.Value = _a_cnt; OdbcParameter paramB = odbcCommand.Parameters.Add("@B_CNT", OdbcType.Int); paramB.Value = _b_cnt; try { odbcCommand.ExecuteNonQuery(); } catch...

Calling stored procedures from database toolbox
Hi there When I call a stored procedure using exec() function, can I pass cell array as paramters in the function? My understanding is not,since the parameter is for the stored procedure on the database sever, therefore the database server cannot understand cell array. Is it correct? Thanks a lot, Ding ...

Re: calling oracle stored procedures
Scott: The SELECT statement that is executing a stored procedure is actually executing on the Oracle server (pass-thru query). I don't believe that a Data step would be a viable alternative. Alternative Oracle stored procedures might be. Sig -----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Miller, Scott Sent: Thursday, September 08, 2005 2:40 PM To: SAS-L (E-mail) Subject: calling oracle stored procedures i am pulling data from oracle tables where some fields in each table are encrypted, and the oracle dba has a stored proc...

Oracle Database and JavaMail in Stored Procedure?
Can I access JavaMail from the Oracle Database (not the App Server)? I am new to Java in Oracle database - any exampl,es out there of using JavaMail from Oracle (say, from a stroed procedure?) In answer to my own question, I have gotten JavaMail to work from an Oracle Stored procedure. There is an execellent walk thru of just such a thing in wrox Press 'Oracle 9i Java Programming'. Works quite easily actually. ...

call a stored procedure on Oracle server
Hi, I am working on a SAS program which needs to call a stored procedure on Oracle server. Here are the codes in my program. proc sql; connect to Oracle (user =aa orapw=bb path='cc'); %put &sqlxmsg; create table all as select * from Connection to Oracle (EXECUTE (EXECUTE stored_proc1('Diabetes Study', 'weight'))) ; %put &sqlxmsg; quit; This is the error message I got. ERROR: ORACLE prepare error: ORA-24333: zero iteration count. I am wondering if you could give me some help on this and show me the codes that work for you. Thanks, Carol ...

call Oracle stored procedure from ASP
Hello, I am trying to call a stored procedure from ASP. Below is the code I wrote: Sub initDB() Set DBConn = Server.CreateObject("ADODB.Connection") DBConn.ConnectionTimeout = Session("Conn1_ConnectionTimeout") DBConn.CommandTimeout = Session("Conn1_CommandTimeout") DBConn.Open "DSN=AUTOSYSP01","autosys","autosys" End Sub Sub initRecordset1() Set cmdTmp = Server.CreateObject("ADODB.Command") Set cmdTmp.ActiveConnection = DBConn cmdTmp.CommandTimeout = 10 cmdTmp.CommandText = "autosys_doc" cmdTmp.P...

Web resources about - calling a stored procedure from a stored procedure - comp.databases.oracle.misc

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as ‚Äčtubal ligation would violate its religious freedom

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

Resources last updated: 2/19/2016 1:13:29 AM