Re: Executing a procedure from SQL Plus

From: David Bradley <davidbradley_at_home.com>
Date: Fri, 22 Sep 2000 15:53:58 GMT
Message-ID: <qgLy5.194620$6y5.113005746@news2.rdc2.tx.home.com>

Thank you for your solution. Your example did execute the procedure. How can I also display the contents of c_people to the screen?

 Sorry, I am new at some of this stuff.

Thanks,

David

Mark <cdo_at_lambic.co.uk> wrote in message news:39CB791B.F0F1D079_at_lambic.co.uk...
> David Bradley wrote:
> >
> > I have tried all kinds of ways to execute this. My procedure should
 return
> > all rows from a table where the LAST_NAME is like the passed parameter.
 The
> > procedure works fine when called from my VB app. Could you give me the
> > syntax to execute this in SQL Plus window with the declaration of the
> > variables?
>
> DECLARE
> c_people Personality.PeopleCur;
> BEGIN
> Personality.GetNames(c_people, 'B%');
> END;
> /
>
> This will execute your procedure, but you won't be able to do anything
> with the result. Processing of the result cursor should be handled
> inside the anonymous block (after the procedure call and before END;).
>
>
> > Carl Hathaway <chathaw2_at_ford.com> wrote
> > > Additionally as the first parameter is an IN OUT then you need to
 declare
> > > a variable to hold the returning value.
> > >
> > > spencer <spencerp_at_swbell.net> wrote
> > > > the GetNames procedure is defined with two arguments, and you are
> > > > only supplying one argument in the call. that's what oracle is
 telling
> > > > you, that you have supplied only one argument.
> > > >
> > > > "David Bradley" <davidbradley_at_home.com> wrote
> > > > > I created the package and package body below. When I call this
 from
> > > > > my Visual Basic app using ADO it runs fine. When I try to execute
 it
> > > > > from SQLPlus I get the following error. Am I doing something
 wrong.
> > >
> >

 Error ----------------------------------------------------------------------


> > > > >

> > > > > SQLWKS> execute Personality.GetNames('B%');

> > > > > Personality.GetNames('B%');

> > > > > *

> > > > > ORA-06550: line 2, column 2:
> > > > > PLS-00306: wrong number or types of arguments in call to
 'GETNAMES'
> > > > > ORA-06550: line 2, column 2:
> > > > > PL/SQL: Statement ignored
> > > > >
> > > > >
> > > > >
> > > > > -- Creation of package and package body
> > > > >
> > > > > CREATE OR REPLACE PACKAGE Personality
> > > > > IS
> > > > > TYPE PeopleCur IS REF CURSOR RETURN sti_personnel%ROWTYPE;
> > > > > PROCEDURE GetNames(PeopleCursor in out PeopleCur, plastname in
> > > > > sti_personnel.LAST_NAME%TYPE);
> > > > > END Personality;
> > > > >
> > > > > /
> > > > >
> > > > >
> > > > > CREATE OR REPLACE PACKAGE BODY Personality
> > > > > AS
> > > > >
> > > > > PROCEDURE GetNames(PeopleCursor in out Peoplecur, plastname in
> > > > > sti_personnel.LAST_NAME%TYPE)
> > > > > IS
> > > > > BEGIN
> > > > >
> > > > > OPEN PeopleCursor FOR
> > > > > SELECT * FROM sti_personnel WHERE LAST_NAME like plastname ;
> > > > >
> > > > > END GetNames;
> > > > >
> > > > > END Personality;
Received on Fri Sep 22 2000 - 10:53:58 CDT