PL/SQL Return Cursor

Question

I am trying to define the PL/SQL function

CREATE OR REPLACE FUNCTION B2BOWNER.F_SSC_Page_Map_Select(
                            p_page_id   IN B2BOWNER.SSC_Page_Map.PAGE_ID_NBR%TYPE, 
                            p_page_type IN B2BOWNER.SSC_Page_Map.PAGE_TYPE%TYPE, 
                            p_page_dcpn IN B2BOWNER.SSC_Page_Map.PAGE_DCPN%TYPE)

RETURN MAP_REC

AS

   CURSOR MAP_CURSOR IS

   SELECT * 

   FROM B2BOWNER.SSC_PAGE_MAP 

   WHERE PAGE_ID_NBR = p_page_id AND PAGE_TYPE = p_page_type;

   MAP_REC MAP_CURSOR%ROWTYPE;

   TABLE_DOES_NOT_EXIST exception;  
   PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942

BEGIN

   FOR MAP_REC IN MAP_CURSOR

   LOOP

       System.out.println("ID: " + MAP_REC.PAGE_ID_NBR + " " + "TYPE: " + MAP_REC.PAGE_TYPE + " " + "DCPN: " + MAP_REC.PAGE_DCPN);

   END LOOP;

   RETURN MAP_REC;

   EXCEPTION

       WHEN TABLE_DOES_NOT_EXIST THEN

           RETURN -1;

       WHEN DUP_VAL_ON_INDEX THEN

           RETURN -2;

       WHEN INVALID_NUMBER THEN

           RETURN -3;

       WHEN OTHERS THEN

           RETURN -4;

END F_SSC_Page_Map_Select;

SHOW ERRORS PROCEDURE B2BOWNER.F_SSC_Page_Map_Select;

GRANT EXECUTE ON B2BOWNER.F_SSC_Page_Map_Select TO B2B_USER_DBROLE;

and receive the following error

Warning: compiled but with compilation errors
No errors.
Grant complete.

[Warning] ORA-24344: success with compilation error
6/12    PLS-00320: the declaration of the type of this expression is incomplete or malformed
PL/SQL: Compilation unit analysis terminated
(1: 0): Warning: compiled but with compilation errors

Answer 1

A few things. First, the MAP_REC declared in the declaration section is not the same as the MAP_REC used in the cursor FOR loop. This was certainly surprising to me when I first encountered it but it's something we all have to get used to. To do what you're trying to do you'll need to either use the OPEN, FETCH, and CLOSE method of working with a cursor, or else copy the values from the cursor FOR loop variable to the 'declared' variable.

Secondly, you can't return a MAP_REC from this function as MAP_REC is declared inside the function and thus isn't known to the compiler when it processes the function definition. Best to use the specific table ROWTYPE.

Third, later in the code you have RETURN -1, etc, which won't work as a cursor %ROWTYPE variable. I suggest that instead of trying to return 'magic number' values to indicate specific failures you should simply let the exceptions propagate to the caller, who can then handle the exceptions as needed. That's why we have exceptions - to prevent having different error handling schemes for every single subroutine out there.

And finally: this is PL/SQL - we don't System.out.println here. :-)

A possible way to rewrite your code would be:

CREATE OR REPLACE FUNCTION B2BOWNER.F_SSC_Page_Map_Select(
                            p_page_id   IN B2BOWNER.SSC_Page_Map.PAGE_ID_NBR%TYPE, 
                            p_page_type IN B2BOWNER.SSC_Page_Map.PAGE_TYPE%TYPE, 
                            p_page_dcpn IN B2BOWNER.SSC_Page_Map.PAGE_DCPN%TYPE)
  RETURN B2BOWNER.SSC_PAGE_MAP%ROWTYPE
AS
  CURSOR MAP_CURSOR IS
    SELECT * 
      FROM B2BOWNER.SSC_PAGE_MAP 
      WHERE PAGE_ID_NBR = p_page_id AND
            PAGE_TYPE = p_page_type;

  MAP_REC       B2BOWNER.SSC_PAGE_MAP%ROWTYPE;
  bCursor_open  BOOLEAN := FALSE;
BEGIN
  OPEN MAP_CURSOR;
  bCursor_open := TRUE;

  LOOP
    FETCH MAP_CURSOR
      INTO MAP_REC;
    EXIT WHEN MAP_CURSOR%NOT_FOUND;

    DBMS_OUTPUT.PUT_LINE('ID: ' || MAP_REC.PAGE_ID_NBR || ' ' || 'TYPE: ' || 
                         MAP_REC.PAGE_TYPE || ' ' || 'DCPN: ' || MAP_REC.PAGE_DCPN);
  END LOOP;

  CLOSE MAP_CURSOR;
  bCursor_open := FALSE;

  RETURN MAP_REC;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in F_SSC_Page_Map_Select: ' || SQLCODE || ' ' ||
                         SQLERRM);

    IF bCursor_open THEN
      CLOSE MAP_CURSOR;
    END IF;

    RAISE;
END F_SSC_Page_Map_Select;

Best of luck.

Share and enjoy.