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: <codefragment_at_googlemail.com>
Date: Fri, 29 Jun 2007 07:45:48 -0700
Message-ID: <1183128348.929332.136630@n2g2000hse.googlegroups.com>


On 29 Jun, 14:53, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 29, 3: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- Hide quoted text -
>
> > - Show quoted text -
> >From SQL*Plus:
>
> SQL> variable bob refcursor
> SQL> exec code1.code1(bob,1,1)
>
> Should then work.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

That didn't quite work but this did:

variable C refcursor
exec code1.code1(:C, 1, 1)
print C

(found it on a website),

Thanks for everyones help. I've always found once you get past hello world things get easier regardless of the language so this should be a big help Received on Fri Jun 29 2007 - 09:45:48 CDT

Original text of this message

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