Oracle SQL Developer PL/SQL return an array

You can also open a ref_cursor for a string value. Please take a look at this:

CREATE OR REPLACE PROCEDURE GetCol(PARAM IN VARCHAR2, recordset OUT sys_refcursor)ASQRY varchar2(100);BEGINQRY := 'SELECT DISTINCT '||PARAM||' FROM my_table';OPEN recordset FOR QRY;END;

Then:

DECLARE  l_cursor  SYS_REFCURSOR;  l_sname VARCHAR2(50);BEGIN  GetCol('col_name',l_cursor);    LOOP     FETCH l_cursor INTO  l_sname;    EXIT WHEN l_cursor%NOTFOUND;    DBMS_OUTPUT.PUT_LINE(l_sname);  END LOOP;END;

Your problem is caused by ambiguity about what PARAM is in the procedure's SELECT statement:

CREATE OR REPLACEPROCEDURE GetCol(PARAM IN STRING, recordset OUT sys_refcursor)ASBEGINOPEN recordset FORSELECT DISTINCT(PARAM) -- Ambiguity hereFROM my_table;END;

Does PARAM refer to the table column or to the first parameter of the procedure? Oracle has assumed the parameter. You can explicitly say which like this:

SELECT DISTINCT(my_table.PARAM) FROM my_table;

You could if appropriate (it probably isn't here) specify the procedure parameter instead:

SELECT DISTINCT(GetCol.PARAM) FROM my_table;

Generally this is best avoided by:

• always using table aliases in column references select statements, and
• having a standard for parameter names that makes them less likely to clash e.g. P_PARAM.