CODEWITHSUNDEEP
oracle-databaseplsql
FreeBirdForever
FreeBirdForever

Reputation: 23

PL/SQL Return Types of Result Set Variables or Query Do Not Match

I am trying to use a GET stored procedure/cursor to show the GAME NAME of a Lottery Game Table I created in a lottery game database.

Here is the code: 

CREATE OR REPLACE PROCEDURE GetLotteryGameName (
  p_lgid IN NUMBER,
  p_value out VARCHAR2,
  p_field IN VARCHAR2
)
IS
BEGIN
  SELECT GAMENAME
  INTO p_value
  FROM LOTTERYGAME
  WHERE LOTTERYGAMEID = p_lgid;
END GetLotteryGameName;

CREATE OR REPLACE PACKAGE GETLOTTERYGAMENAMEPKG IS
  PROCEDURE GetLotteryGameName (
    p_lgid IN NUMBER,
    p_value out VARCHAR2,
    p_field IN VARCHAR2
  );

  TYPE per_ref_cursor IS REF CURSOR;
  PROCEDURE GetGameName (p_lgid IN NUMBER, p_ref OUT per_ref_cursor);

END GETLOTTERYGAMENAMEPKG;

CREATE OR REPLACE PACKAGE BODY GETLOTTERYGAMENAMEPKG IS
  PROCEDURE GetLotteryGameName (
    p_lgid IN NUMBER,
    p_value out VARCHAR2,
    p_field IN VARCHAR2
  )
  IS
  BEGIN
    SELECT GAMENAME
    INTO p_value
    FROM LOTTERYGAME
    WHERE LOTTERYGAMEID = p_lgid;
  END GetLotteryGameName;

  PROCEDURE GetName
    (p_lgid IN NUMBER,
     p_ref OUT per_ref_cursor) IS
     BEGIN
      OPEN p_ref FOR
        SELECT GAMENAME
        FROM LOTTERYGAME
        WHERE LOTTERYGAMEID = p_lgid;
    END GetName;

END GETLOTTERYGAMENAMEPKG;

DECLARE
  v_cursor    GETLOTTERYGAMENAMEPKG.per_ref_cursor;
  v_lgid      LOTTERYGAME.LOTTERYGAMEID%TYPE;
  v_gamename  LOTTERYGAME.GAMENAME%TYPE;

BEGIN
  GETLOTTERYGAMENAMEPKG.GetName (p_lgid = 2,
                                 p_ref => v_cursor);
  LOOP
    FETCH v_cursor
    INTO v_lgid, v_gamename;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_lgid || ',' || v_gamename);
  END LOOP;
  CLOSE v_cursor;
END;

When I run the above declare statement, I get the following error:

06504. 00000 -  "PL/SQL: Return types of Result Set variables or query do not match"
*Cause:    Number and/or types of columns in a query does not match declared
           return type of a result set  variable, or declared types of two Result
           Set variables do not match.

I know there is an error somewhere in this code, I just don't know what I'm looking for, or how to set myself up to avoid future failures like this.

Any help is much appreciated,

Cheers,

FBF

Upvotes: 1

Views: 10765

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You should also correct the syntax error in:

GETLOTTERYGAMENAMEPKG.GetName (p_lgid = 2,
                               p_ref => v_cursor);

which should read

GETLOTTERYGAMENAMEPKG.GetName (p_lgid => 2,
                               p_ref => v_cursor);

Share and enjoy.

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

As the error says, you SELECT only GAMENAME in your SELECT but attempt to put into v_lgid, v_gamename.

So adding LOTTERYGAMEID to your CURSOR's SELECT

Or Change your anonymous block.

  PROCEDURE GetName
    (p_lgid IN NUMBER,
     p_ref OUT per_ref_cursor) IS
     BEGIN
      OPEN p_ref FOR
        SELECT GAMENAME,LOTTERYGAMEID
        FROM LOTTERYGAME
        WHERE LOTTERYGAMEID = p_lgid;
    END GetName;

Upvotes: 5

Related Questions