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?
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