Oracle SQL Developer PL/SQL return an array

Question

Devs,

I've searched everywhere I can, but I could not find solution to this simple problem.

Situation:

I need to write a procedure where it takes a column name as the input and return all the distinct values present in that column as output. And then I have to use this procedure in some c# code.

In MS server, it is very easy as it will directly give the set of results unlike in PL/SQL.

Script I could write (which is not giving me the result I need):

CREATE OR REPLACE
PROCEDURE GetCol(PARAM IN STRING, recordset OUT sys_refcursor)
AS
BEGIN
OPEN recordset FOR
SELECT DISTINCT(PARAM)
FROM my_table;
END

;

When I try to check the data in the recordset using this code:

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;
  CLOSE 

Can someone help me with this code please.

Answer 1

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)
AS
QRY varchar2(100);
BEGIN
QRY := '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;