Re: Executing a procedure from SQL Plus

From: Ghost <thomp901_at_micron.net>
Date: Sat, 23 Sep 2000 15:32:38 -0600
Message-ID: <39CD2176.DFC7CFF2@micron.net>

DECLARE
  c_people Personality.PeopleCur;
BEGIN

  Personality.GetNames(c_people, 'B%');
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE(c_people);

END;
/

I'd recommend the Oracle Complete Reference, it covered all the problems you were having.

David Bradley wrote:
>
> 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;
 

-- 
---------------------------------------------------------------------
Chad Thompson, 
Programmer Analyst: VB, VC++, PLSQL, Oracle HRMS, Security Apps
                            Home: thomp901_at_micron.net

Thou shalt not tick off the dragon.... for thou art crunchy and 
taste good with ketchup.
---------------------------------------------------------------------
--

Received on Sat Sep 23 2000 - 16:32:38 CDT