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.

The logic looks correct, but your select statement is strange. If you run "select distinct('col_name') from my_table" what do you get? You need to do an execute immediate instead. — OldProgrammer
– OldProgrammer, Commented Feb 6, 2015 at 17:58
I just need to get all the unique values from that particular column. — Gagan Bodduluru
– Gagan Bodduluru, Commented Feb 6, 2015 at 18:12

Aramillo · Accepted Answer · 2015-02-06 18:49:46Z

3

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;

answered Feb 6, 2015 at 18:49

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Andrews · Accepted Answer · 2015-02-06 18:12:04Z

2

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

CREATE OR REPLACE
PROCEDURE GetCol(PARAM IN STRING, recordset OUT sys_refcursor)
AS
BEGIN
OPEN recordset FOR
SELECT DISTINCT(PARAM) -- Ambiguity here
FROM 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.

answered Feb 6, 2015 at 18:12

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

1 Comment

Gagan Bodduluru Over a year ago

PARAM here, is the column name and also the only IN parameter for the procedure. When ever user gives a column name, procedure should return all the distinct values present in that particular column.

Collectives™ on Stack Overflow

Oracle SQL Developer PL/SQL return an array

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related