Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: I just want to run a stored procedure...
On Jun 29, 4:13 am, codefragm..._at_googlemail.com wrote:
> On 28 Jun, 17:35, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
> > On Jun 28, 11:24 am, codefragm..._at_googlemail.com wrote:
>
> > > Hi
> > > I have a stored procedure that I want to run.The shell of it is
> > > included at the bottom of this
> > > post. I try running the below in sqlplusw and get the following
> > > result.
> > > Can you just run stored procedures in oracle in the same manner you
> > > can in query analyser
> > > in MS Sql?
>
> > > thanks
>
> > > > exec CODE0 (1,1)
> > > BEGIN CODE0; END;
>
> > > *
> > > ERROR at line 1:
> > > ORA-06550: line 1, column 7:
> > > PLS-00201: identifier 'CODE0' must be declared
> > > ORA-06550: line 1, column 7:
> > > PL/SQL: Statement ignored
>
> > > CREATE OR REPLACE PACKAGE "CODE0" AS
> > > TYPE CUR IS REF CURSOR;
> > > PROCEDURE CODE0 (pCUR1 OUT CUR,
> > > pARG1 IN NUMBER,
> > > pARG2 IN NUMBER);
> > > END CODE0;
> > > GO
>
> > > CREATE OR REPLACE PACKAGE BODY "CODE0" AS
> > > PROCEDURE CODE0 (pCUR1 OUT CUR,
> > > pARG1 IN NUMBER,
> > > pARG2 IN NUMBER) IS
> > > BEGIN
> > > DECLARE
> > > pLocal1 NUMBER(2);
> > > BEGIN
> > > pLocal2 := ISVISIBILITYENABLED();
>
> > > OPEN pCUR1 FOR
>
> > > <--------sql here------->
>
> > > END;
> > > END CODE0;
> > > END CODE0;
> > > GO
>
> > You can't run a package, only the procedures and functions within it:
>
> > exec code0.code0(1,1)
>
> > And you can't run a procedure or function declared in a package
> > without using the package name. Unfortunately you've named your
> > package the same as your procedure; not the best practice to maintain.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for the help, nearly there. The problem seems to be the cursor.
> I'm at the "hello world" level with oracle (better in MS SQL) so
> I guess I just need to declare one? Not sure of the syntax though
> Any help appreciated, once I'm past this point then (in my head at
> least) I can get on and actually get into Oracle
>
> > exec code1.code1(1,1)
> BEGIN code1.code1(1,1); END;
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to
> 'ABSENCECODE1'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
You're not passing in a cursor variable into which you can place the cursor opened in your procedure. That is why you get the descriptive error message above. Try...
Regards,
Steve Received on Fri Jun 29 2007 - 08:54:32 CDT