Cannot Iterate Dynamically Created SYS_REFCURSOR in Oracle Function

Question

I am facing an issue with a function that I'm working on. The function aims to retrieve a column value based on the input parameters and a requested column name of that table. However, when I try to compile its code, which is the following:

CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
    IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
    IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
    IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
    IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
    IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2 IS
    C_TEST_TABLE SYS_REFCURSOR;
    RESULT       VARCHAR2(255);
BEGIN
    IF IN_COLUMN4 IS NULL THEN
        OPEN C_TEST_TABLE FOR
            SELECT *
            FROM TEST_TABLE
            WHERE COLUMN1 = COLUMN1
              AND COLUMN2 = IN_COLUMN2
              AND COLUMN3 = IN_COLUMN3;
    ELSIF IN_COLUMN4 IS NOT NULL THEN
        OPEN C_TEST_TABLE FOR
            SELECT *
            FROM TEST_TABLE
            WHERE COLUMN1 = COLUMN1
              AND COLUMN2 = IN_COLUMN2
              AND COLUMN3 = IN_COLUMN3
              AND COLUMN4 = IN_COLUMN4;
    END IF;
    FOR C_TEST_TABLE_REC IN C_TEST_TABLE
        LOOP
            IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
                RESULT := C_TEST_TABLE_REC.COLUMN1;
                EXIT;
            ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
                RESULT := C_TEST_TABLE_REC.COLUMN2;
                EXIT;
            ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
                RESULT := C_TEST_TABLE_REC.COLUMN3;
                EXIT;
            END IF;
        END LOOP;
    RETURN RESULT;
END;

I receive the following error:

[Warning] ORA-24344: success with compilation error 19/27 PLS-00221: 'C_TEST_TABLE' is not a procedure or is undefined 19/5 PL/SQL: Statement ignored (1: 0): Warning: compiled but with compilation errors

that indicates that C_TEST_TABLE cannot be used after the declaration block

Answer 1

You don't need to use cursors:

CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
    IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
    IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
    IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
    IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
    IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2 IS
    result VARCHAR2(255);
BEGIN
  SELECT CASE IN_REQUESTED_COLUMN
         WHEN 'COLUMN1' THEN column1
         WHEN 'COLUMN2' THEN column2
         WHEN 'COLUMN3' THEN column3
         END
  INTO   result
  FROM   TEST_TABLE
  WHERE  COLUMN1 = IN_COLUMN1
  AND    COLUMN2 = IN_COLUMN2
  AND    COLUMN3 = IN_COLUMN3
  AND    (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4)
  FETCH FIRST ROW ONLY;

  RETURN result;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

Which, for the sample data:

CREATE TABLE test_table (column1, column2, column3, column4) AS
SELECT 'A', 'B', 'C', 'D' FROM DUAL UNION ALL
SELECT 'A', 'B', 'C', 'E' FROM DUAL;

Then:

SELECT FIND_TEST_TABLE_COLUMN('A', 'B', 'C', 'D', 'COLUMN1') AS result1,
       FIND_TEST_TABLE_COLUMN('A', 'B', 'C', NULL, 'COLUMN2') AS result2,
       FIND_TEST_TABLE_COLUMN('X', 'Y', 'Z', NULL, 'COLUMN3') AS result3
FROM   DUAL;

Outputs:

RESULT1	RESULT2	RESULT3
A	B	null

---

If you want to fix your code then you need to declare the variable C_TEST_TABLE_REC and use FETCH (and can remove the loop and simplify the code to):

CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
    IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
    IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
    IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
    IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
    IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2
IS
  C_TEST_TABLE     SYS_REFCURSOR;
  C_TEST_TABLE_REC TEST_TABLE%ROWTYPE;
BEGIN
  OPEN C_TEST_TABLE FOR
    SELECT *
    FROM   TEST_TABLE
    WHERE  COLUMN1 = IN_COLUMN1
      AND  COLUMN2 = IN_COLUMN2
      AND  COLUMN3 = IN_COLUMN3
      AND  (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4);

  FETCH C_TEST_TABLE INTO C_TEST_TABLE_REC;

  IF C_TEST_TABLE%NOTFOUND THEN
    RETURN NULL;
  END IF;

  IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
    RETURN C_TEST_TABLE_REC.COLUMN1;
  ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
    RETURN C_TEST_TABLE_REC.COLUMN2;
  ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
    RETURN C_TEST_TABLE_REC.COLUMN3;
  ELSE
    RETURN NULL;
  END IF;
END;
/

fiddle