Using ref cursor vs sys_refcursor inside a Stored Procedure Parameters

I'm not sure what you mean by

create or replace procedure GetEmployeesInDept( c out ref cursor)

I've not seen this before and I can't get a procedure declared like this to compile. Could you please provide sample code or links to where you've seen this before?

What you might have seen is something like the following:

CREATE OR REPLACE PACKAGE demo AS

  TYPE ref_cursor IS REF CURSOR;

  PROCEDURE GetEmployeesInDept(c OUT ref_cursor);

END demo;
/

CREATE OR REPLACE PACKAGE BODY demo AS

  PROCEDURE GetEmployeesInDept(c OUT ref_cursor)
  IS
  BEGIN
    RAISE NO_DATA_FOUND;
  END GetEmployeesInDept;

END demo;
/

In this case, we declare a type to be a REF CURSOR, and use it as an OUT parameter in a stored procedure.

There is no difference between using a type declared as REF CURSOR and using SYS_REFCURSOR, because SYS_REFCURSOR is defined in the STANDARD package as a REF CURSOR in the same way that we declared the type ref_cursor. In fact, if you're using Oracle 9i or later, look within your Oracle database installation, in %ORACLE_HOME%\rdbms\admin\stdspec.sql, and you should find the following line somewhere in there:

type sys_refcursor is ref cursor;

SYS_REFCURSOR was introduced in Oracle 9i. You may find various types declared as REF CURSOR in PL/SQL code that was written before Oracle 9i was released.

There can be one difference between the two, I can think off is ref_cursor can be STRONG or WEAK type whereas SYS_REFCURSOR is always weak type as it is defined that way.

There are two forms of ref Cursor the strong REF CURSOR and the weak REF CURSOR. PL/SQL is a statically typed language, and the weak REF CURSOR is one of the few dynamically typed constructs supported.

(statically typed language: that was mean that type checking is performed at compile time not at runtime)

When you defined a SYS_REFCURSOR is a predefined weak REF CURSOR type.

A strong ref cursor it's something like this:

TYPE book_data_t IS REF CURSOR RETURN book%ROWTYPE;

cause associate the cursor variable with a specific record structure.