CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlprocedurecursors
Gagan Bodduluru
Gagan Bodduluru

Reputation: 43

Oracle SQL Developer PL/SQL return an array

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.

Upvotes: 3

Views: 1535

Answers (2)

Aramillo
Aramillo

Reputation: 3216

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;

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132580

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.

Upvotes: 2

Related Questions