Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: I just want to run a stored procedure...

Re: I just want to run a stored procedure...

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Fri, 29 Jun 2007 13:54:32 -0000
Message-ID: <1183125272.235913.237610@m36g2000hse.googlegroups.com>


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...

http://www.oracle.com/pls/db102/to_toc?pathname=appdev.102%2Fb14261%2Ftoc.htm&remark=portal+%28Books%29

Regards,

Steve Received on Fri Jun 29 2007 - 08:54:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US