I have the following package header and body

Header:
--------------

CREATE OR REPLACE PACKAGE WDF_REPLENISHMENT
as
TYPE T_CURSOR is REF CURSOR;
PROCEDURE UserDetails(
RefCursorOutput IN OUT T_CURSOR,
UserName IN VARCHAR2,
UserPassword IN VARCHAR2 );
END WDF_REPLENISHMENT;

Body:
----------------------

CREATE OR REPLACE PACKAGE BODY WDF_REPLENISHMENT
AS PROCEDURE UserDetails(
RefCursorOutput IN OUT T_CURSOR,
UserName IN VARCHAR2,
UserPassword IN VARCHAR2 )
IS
TempRefCursor T_CURSOR;

BEGIN
OPEN TempRefCursor FOR
Select distinct u.usr_tech_key, grp_tech_key,
bas_user_name from basusree u, basuguee g
where u.usr_tech_key = g.usr_tech_key And
usr_signon = UserName And usr_secure = UserPassword;
RefCursorOutput := TempRefCursor;
END UserDetails;
END WDF_REPLENISHMENT;


It compiles just fine. But when I try to execute it from SQLPLUS this is what I get.....

SQL> var xyz refcursor
SQL> exec wdf_replenishment.UserDetails(:xyz,'jdauser','JDAU SER');

PL/SQL procedure successfully completed.

SQL> print xyz

no rows selected


However, If I run just the SELECT from SQLPLUS, it does come back with one row

SQL> l
1 Select distinct u.usr_tech_key, grp_tech_key,
2 bas_user_name from basusree u, basuguee g
3 where u.usr_tech_key = g.usr_tech_key And
4 usr_signon = 'jdauser'
5* and usr_secure = 'JDAUSER'
SQL> /

USR_TECH_KEY GRP_TECH_KEY BAS_USER_NAME
------------ ------------ ----------------------------------------
2 3 JDA ODBMS User

1 row selected.


Obviously, there's something wrong with the way I am using ref cursors.
Could someone please point it out to me, as I have been unable to figure it out after wasting much time.

Thanks in advance
pusht

I don't know what the problem is, but there appears to be nothing wrong with your use of REF CURSORS. Here is a simple example I made up based on yours:

I think the problem must be somewhere else. I wonder: does either of the tables basusree or basuguee have a column called USERNAME or USERPASSWORD? That would do it.

thanks Andrew!!

But, neither of the tables has a column called USERNAME or USERPASSWORD.

:-(

Shame!

Well, then to debug I would start by simplifying your code as far as possible - get rid of the ref cursor, package etc. and just try this:

var UserName VARCHAR2(30)
var UserPassword IN VARCHAR2(30)

exec :UserName := 'jdauser';
exec :UserPassword := 'JDAUSER':

Select distinct u.usr_tech_key, grp_tech_key,
bas_user_name
from basusree u, basuguee g
where u.usr_tech_key = g.usr_tech_key And
usr_signon = :UserName And usr_secure = :UserPassword;