parsing table values into function

Question

i'm hoping to get some advice for this Oracle PL/SQL problem. I've been getting the error at the bottom for quite some time and i'm not sure what seems to be the issue. I'm trying to parse in table values into the function and ouput the values from the function's return value

SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE OR REPLACE FUNCTION LISTNATION(RegionName VARCHAR2)
RETURN VARCHAR2
IS
Result VARCHAR2(500);
BEGIN   
    Result := RegionName || ': ';
    FOR aRow IN (SELECT r_regionkey, r_name, n_name 
            FROM nation, region 
                WHERE n_regionkey = r_regionkey
            AND r_name = RegionName)
    LOOP
        Result := Result || aRow.n_name || ', ';
    END LOOP;

    -- RETURN RESULTS
    RETURN Result;

END LISTNATION;
/

BEGIN
    DBMS_OUTPUT.PUT_LINE(LISTNATION(r_name));
END;
/

Here's there error

SQL> BEGIN
      2          DBMS_OUTPUT.PUT_LINE(LISTNATION(r_name));
      3  END;
      4  /
        DBMS_OUTPUT.PUT_LINE(LISTNATION(r_name));
                                        *
    ERROR at line 2:
    ORA-06550: line 2, column 34:
    PLS-00201: identifier 'R_NAME' must be declared
    ORA-06550: line 2, column 2:
    PL/SQL: Statement ignored

user_9097906 · Answer

You have to pass value to the function, but - declare it first.

DECLARE
  r_name    region.r_name%type := 'Ruhr';         --> this
BEGIN
    DBMS_OUTPUT.PUT_LINE(LISTNATION(r_name));
END;
/

parsing table values into function

1 Answers