Here is how I defined my ref cursor:



create or replace PACKAGE SPRINGAPP_REFCURSOR_PKG AS

TYPE LOGIN_NAME IS RECORD(FNAME SPRINGAPP.LOGIN_USER.FIRST_NAME%TYPE, LNAME SPRINGAPP.LOGIN_USER.LAST_NAME%TYPE);

TYPE LOGIN_NAME_CURSOR IS REF CURSOR RETURN LOGIN_NAME;

END SPRINGAPP_REFCURSOR_PKG;



Here is my function code:



CREATE OR REPLACE

FUNCTION Get_Login_User_Func (USER_NAME SPRINGAPP.LOGIN_USER.FIRST_NAME%TYPE)

RETURN

SPRINGAPP.SPRINGAPP_REFCURSOR_PKG.LOGIN_NAME_CURSOR

IS

USER_INFO SPRINGAPP.SPRINGAPP_REFCURSOR_PKG.LOGIN_NAME_CURSOR;

BEGIN

OPEN USER_INFO FOR SELECT FIRST_NAME, LAST_NAME FROM LOGIN_USER WHERE USERNAME = USER_NAME;

RETURN USER_INFO;

EXCEPTION

WHEN OTHERS THEN

IF (USER_INFO%isOpen) THEN

CLOSE USER_INFO;

END IF;

RAISE;

END Get_Login_User_Func;



Now if I call my function in the following code:



DECLARE

USER_NAME VARCHAR2(255);

mycursor SPRINGAPP.SPRINGAPP_REFCURSOR_PKG.LOGIN_NAME_CURSOR;

BEGIN

USER_NAME := 'jay';



mycursor := GET_LOGIN_USER_FUNC(

USER_NAME

);



FOR rs IN mycursor

LOOP

DBMS_OUTPUT.PUT_LINE('FIRST NAME = ' || rs.FNAME);

DBMS_OUTPUT.PUT_LINE('LAST NAME = ' || rs.LNAME);

END LOOP;



END;



I got the following oracle error:

ORA-06550: line 11, column 13:
PLS-00221: 'MYCURSOR' is not a procedure or is undefined
ORA-06550: line 11, column3:
PL/SQL: Statement ignored
ORA-06512: at line 12

However, it works if I do:

DECLARE

USER_NAME VARCHAR2(255);
mycursor SPRINGAPP.SPRINGAPP_REFCURSOR_PKG.LOGIN_NAME_CURSOR;
rs SPRINGAPP.SPRINGAPP_REFCURSOR_PKG.LOGIN_NAME
BEGIN
USER_NAME := 'jay';

mycursor := GET_LOGIN_USER_FUNC(
USER_NAME
);

LOOP
FETCH mycursor into rs
EXIT WHEN mycursor%NOTFOUND

DBMS_OUTPUT.PUT_LINE('FIRST NAME = ' || rs.FNAME);
DBMS_OUTPUT.PUT_LINE('LAST NAME = ' || rs.LNAME);

END LOOP;
CLOSE mycursor;
END;

Question is WHY????