for loop inside a cursor oracle

Question

I have created a TYPE

TYPE t_array IS TABLE OF VARCHAR2(15);

A function which takes a string which contains ',' as delimiter and the function returns t_array which is basically splitting the string and returning list of values.

FUNCTION split_string(id IN VARCHAR2)
...
...
....
RETURN t_array;
END split_string;

Now my stored procedure takes in the long string as input, calls the function to split the string and loops through the t_array and returns a CURSOR.

PROCEDURE p_get_xxx(p_id IN VARCHAR2,
                    p_cur_result OUT SYSREFCURSOR)
AS
  l_array schema_name.t_array;
BEGIN
  l_array := split_string(p_id);

  OPEN p_cur_result FOR

  FOR i IN l_array.first .. l_array.last
  LOOP
    SELECT * FROM ........
    WHERE ID = l_array(i);   
  END LOOP;
END p_get_xxx;

I get a compilation error along the lines of :

Unexpected 'FOR' in the place of '('

Is there a better way of handling this scenario or am I missing some thing here?

That plsql code looks very school book-ey. Just this part "OPEN p_cur_result FOR for i IN l_array.first .. l_array.last" is just way wrong and looks like copy paste from different examples. — Olafur Tryggvason, Jan 27 '16 at 01:32
http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql — OldProgrammer, Jan 27 '16 at 02:03

score 0 · Accepted Answer · answered Jan 27 '16 at 02:04

0

A cursor is always opened on a SELECT statement. There is no a way, as far as I am aware, to open a cursor on a FOR loop.

It looks to me like you really want to create a SELECT statement dynamically. I suggest something like the following:

PROCEDURE p_get_xxx(p_id IN VARCHAR2, p_cur_result OUT SYSREFCURSOR)
AS
  l_array schema_name.t_array;
  strSelect_statement  VARCHAR2(4000);
BEGIN
  l_array := split_string(p_id);

  -- Set up the basics of the SELECT statement

  strSelect_statement := 'SELECT * FROM SOME_TABLE WHERE ID IN (';

  FOR i IN l_array.FIRST..l_array.LAST LOOP
    strSelect_statement := strSelect_statement ||
                             '''' || l_array(i) || ''',';
  END LOOP;

  -- Get rid of the unwanted trailing comma

  strSelect_statement := SUBSTR(strSelect_statement, 1,
                                  LENGTH(strSelect_statement)-1);

  -- Add a right parentheses to close the IN list

  strSelect_statement := strSelect_statement || ')';

  -- Open the cursor

  OPEN p_cur_result FOR strSelect_statement;
END p_get_xxx;

Best of luck.

answered Jan 27 '16 at 02:04

Bob Jarvis - Слава Україні

48,992
9
77
110

Thank you for the response. My SELECT statement is close to 100 lines - I'll try this approach and keep you posted if it works. – Rohith Jan 27 '16 at 02:26
I tried this approach and printed out the sql generated after all the looping and when I run the sql on my editor, it works as expected. But when I run the actual procedure, somehow the CURSOR is only returning the records related to last element in the array. Did anyone face this kind of issue with CURSORS? – Rohith Jan 27 '16 at 17:07
I suggest that you post another question to address this issue including all the code involved - both the PL/SQL which creates the SQL statement and the generated SQL statement itself. Thanks. – Bob Jarvis - Слава Україні Jan 27 '16 at 17:28
Posted another question as suggested http://stackoverflow.com/questions/35045410/oracle-cursor-not-returning-the-complete-resultset. – Rohith Jan 27 '16 at 18:39

for loop inside a cursor oracle

1 Answers1

Linked