TO_CURSOR_NUMBER v17

Suggest edits

The TO_CURSOR_NUMBER function transforms a ref cursor into a SQL cursor number.

This function is useful to retrieve the metadata (columns, column types, and so forth) from a ref cursor, when you have a SYS_REFCURSOR variable with an unknown return structure.

TO_CURSOR_NUMBER (<rc> IN OUT SYS_REFCURSOR)
    RETURN INTEGER;

Parameters

rc

The ref cursor to be transformed into a cursor number.

Examples

DECLARE
    cur1        SYS_REFCURSOR;
    cur_id      INTEGER;
    tbl_desc    test_tbl%ROWTYPE;
    totcols     INTEGER;
    ret         INTEGER;
    rowcnt      NUMBER;
    col1_data   VARCHAR(30);
    col2_data   VARCHAR(30);
BEGIN
    OPEN cur1 FOR 'SELECT * FROM test_tbl_tbl';
    cur_id:= dbms_sql.to_cursor_number(cur1);

    dbms_sql.define_column(cur_id, 1, tbl_desc.col1);
    dbms_sql.define_column(cur_id, 2, tbl_desc.col2);

    LOOP
      ret := dbms_sql.FETCH_ROWS(cur_id);
      EXIT WHEN ret = 0;
      dbms_sql.column_value(cur_id, 1, tbl_desc.col1);
      dbms_sql.column_value(cur_id, 2, tbl_desc.col2);
      dbms_output.put_line('Col1: ' || tbl_desc.col1 || ' Col2: ' || tbl_desc.col2);
    END LOOP;
END;
← Prev

PARSE

↑ Up

DBMS_SQL

Next →

TO_REFCURSOR