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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 14, 2012 at 10:51

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Yeung Over a year ago

Thank you very much! I did not read the document in detail but I found this behaviours after many trials.

Collectives™ on Stack Overflow

dbms_sql.to_cursor_number - Getting Invalid Cursor error for SYS_REFCURSOR

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related