5

Is there a way to perform a Cursor For Loop with an dynamic SQL-statement?

If I don't want to declare a record I can do something like this (only if I declared the cursor..):

For I in cuSelect Loop
  dbms_output.put_line(I.NAME);
End Loop;

And I can open a cursor for a dynamic SQL-statement:

Open cuSelect For 'Select * From TAB_X';
Fetch ceSelect Into recSelect;
Close cuSelect;

But to do that I have to first declare the Record.

Now my problem is that I have to open the Cursor for a very big and complicated dynamic SQL-statement. The structure of the record is unknown. Is there a way to open a variable cursor and iterate through it with an "undeclared" record?

Improve this question

1 Answer 1

Reset to default
8

I think you can do what you want with DBMS_SQL package.

You can also check these:

For example:

declare
  TYPE curtype IS REF CURSOR;
  src_cur      curtype;
  curid        NUMBER;
  namevar  VARCHAR2(50);
  numvar   NUMBER;
  datevar  DATE;
  desctab  DBMS_SQL.DESC_TAB;
  colcnt   NUMBER;
  dsql varchar2(1000) := 'select card_no from card_table where rownum = 1';
begin
  OPEN src_cur FOR dsql;

  -- Switch from native dynamic SQL to DBMS_SQL package.
  curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);

  -- Define columns.
  FOR i IN 1 .. colcnt LOOP
    IF desctab(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
    END IF;
  END LOOP;

  -- Fetch rows with DBMS_SQL package.
  WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    FOR i IN 1 .. colcnt LOOP
      IF (desctab(i).col_type = 1) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
        dbms_output.put_line(namevar);
      ELSIF (desctab(i).col_type = 2) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
        dbms_output.put_line(numvar);
      ELSIF (desctab(i).col_type = 12) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
        dbms_output.put_line(datevar);
      END IF;
    END LOOP;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(curid);

end;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.