Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14

    Unanswered: trouble using ref cursors

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trouble using ref cursors

    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:

    Code:
    SQL> create or replace package pack is
      2    type t_cursor is ref cursor;
      3    procedure p ( p1 in out t_cursor );
      4  end;
      5  /
    
    Package created.
    
    SQL> create or replace package body pack is
      2    procedure p ( p1 in out t_cursor )
      3    is
      4      r t_cursor;
      5    begin
      6      open r for select * from dept;
      7      p1 := r; /* Note: could have just done "open p1 for ..." */
      8    end;
      9  end;
     10  /
    
    Package body created.
    
    SQL> var x refcursor
    SQL> exec pack.p(:x)
    
    PL/SQL procedure successfully completed.
    
    SQL> print x
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTINGx    NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>
    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.

  3. #3
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    14
    thanks Andrew!!

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

    :-(

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by pusht
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •