Skip to Main Content
  • Questions
  • Ref Cursors - types.cursor_type vs sys_refcursor

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: March 07, 2017 - 10:43 am UTC

Last updated: March 07, 2017 - 2:35 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

There are few stored procedure written which sending OUT REF CURSORS to calling event.
They are using types.cursor_type as datatype.

IS there any difference in types.cursor_type & SYS_REFCURSOR?

and Chris said...

Cursors with a defined return type are strongly typed. Sys_refcursors are weakly typed.

The key difference is you can only assign queries that match the return type to a strong cursor. That is the number and type of the columns have to match (though not the column names). You can open any query with a weakly typed cursor:

create or replace package pkg as
  cursor cur is
    select 1 n from dual;
    
  type tcur is ref cursor return pkg.cur%rowtype;
  
end pkg;
/

declare
  cur pkg.tcur;
begin
  open cur for 
    select 'a' s, 1 n from dual;
end;
/

ORA-06550: line 5, column 5:
PLS-00382: expression is of wrong type

declare
  cur pkg.tcur;
begin
  open cur for 
    select sysdate d from dual;
end;
/

ORA-06550: line 5, column 5:
PLS-00382: expression is of wrong type

declare
  cur pkg.tcur;
begin
  open cur for 
    select 9 d from dual;
end;
/

PL/SQL procedure successfully completed.

declare
  cur sys_refcursor;
begin
  open cur for 
    select 'a' s, 1 n from dual;
    
  close cur;

  open cur for 
    select 9 n from dual;
   
  close cur;
end;
/

PL/SQL procedure successfully completed.


It's better practice to use strong cursors. The query has to match the cursor type. If it doesn't your code won't compile!

Reserve weak cursors for cases where you to return cursors with different numbers or types of columns. Though in my experience the need to do this is rare!

Further reading:

http://docs.oracle.com/database/122/LNPLS/static-sql.htm#LNPLS568

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library