Home » SQL & PL/SQL » SQL & PL/SQL » Help with Stored Procedure
Help with Stored Procedure [message #199159] Fri, 20 October 2006 12:32 Go to next message
bfoley
Messages: 2
Registered: October 2006
Location: Long Island, NY
Junior Member
I created this procedure and can't seem to get it to execute properly in SqlPlus :

CREATE OR REPLACE PACKAGE cdsw_pkg
IS
cursor c1 IS
select dsh_def_fname, dsh_def_lname, num_of_recs
from temp_def_fname ;

TYPE name_cur IS REF CURSOR RETURN c1%rowtype;

PROCEDURE step_01
(
i_Lname IN VARCHAR2,
i_Fname IN VARCHAR2,
o_cursor IN OUT name_cur
);

END cdsw_pkg;



CREATE OR REPLACE PACKAGE BODY cdsw_pkg
AS
PROCEDURE step_01
(
i_Lname IN VARCHAR2,
i_Fname IN VARCHAR2,
o_cursor IN OUT name_cur
)
IS
BEGIN

OPEN o_cursor FOR
SELECT dsh_def_lname,
dsh_def_fname,
num_of_recs
FROM temp_def_fname
WHERE dsh_def_lname = i_Lname
AND dsh_def_fname = i_Fname;

END step_01;

END cdsw_pkg;


Now, when I try to execute this I type this into SqlPlus :

execute cdsw_pkg.step_01('SMITH','MATT')

and I get this as output :

BEGIN cdsw_pkg.step_01('SMITH','MATT'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STEP_01'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Any help would be greatly appreciated.

Brian
Re: Help with Stored Procedure [message #199169 is a reply to message #199159] Fri, 20 October 2006 13:46 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
sql>var rc refcursor
sql>exec cdsw_pkg.step_01('SMITH','MATT', :rc)

And then:

print rc

to have SQL*Plus fetch and display the results (or set autoprint on).

Some comments on the proc:

1) Do you really need a strongly-typed ref cursor?

2) Are you on 9i or later? If so, and you could use a weak cursor, you could use sys_refcursor instead.

3) The ref cursor parameter just needs to be OUT, not IN OUT.
Re: Help with Stored Procedure [message #199170 is a reply to message #199169] Fri, 20 October 2006 13:49 Go to previous message
bfoley
Messages: 2
Registered: October 2006
Location: Long Island, NY
Junior Member
Todd;

That works perfectly. Thank you.

Brian
Previous Topic: ORA-00937Group by / avg problem
Next Topic: Reading the ref cursor value returned by a function
Goto Forum:
  


Current Time: Sun Dec 04 12:59:23 CST 2016

Total time taken to generate the page: 0.11722 seconds