dbms_sql.to_cursor_number - Getting Invalid Cursor error for SYS_REFCURSOR

Question

I have the following code for table and view creation.

create table test_company
(
    comp_id number
    , comp_name varchar2(500)
)
;
insert into test_company values(1, 'CompanyA');
insert into test_company values(2, 'CompanyB');
insert into test_company values(3, 'CompanyC');
create or replace view test_company_view as select * from test_company;

And I have the following code for my cursor testing. But dbms_sql.to_cursor_number got error ORA-01001: invalid cursor

set serveroutput on;
declare
    reader test_company_view%ROWTYPE;
    datacursor SYS_REFCURSOR;
    v_cursor_id number;
begin
    open datacursor for select * from test_company_view;
    v_cursor_id := dbms_sql.to_cursor_number(datacursor); -- ERROR: invalid cursor
    loop fetch datacursor into reader;
        exit when datacursor%NOTFOUND;
        dbms_output.put_line(reader.comp_id);
    end loop;
    close datacursor;
end;

What did I do wrong? Thank you for your helps!

I have tried strongly-typed REF CURSOR and weakly-typed REF CURSOR but they got the same error.

Answer 1

From the documentation:

After you convert a REF CURSOR variable to a SQL cursor number, native dynamic SQL operations cannot access it.

It is not dbms_sql.to_cursor_number that is raising the error, it is fetch datacursor into reader since datacursor can no longer be accessed.