CREATE OR REPLACE PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
END Types;
/

Note: In Oracle9i the SYS_REFCURSOR type has been added making this first step unnecessary. If you are using Oracle9i or later simply ignore this first package and replace any references to Types.cursor_type with SYS_REFCURSOR.

Next a procedure is defined to use the ref cursor:

CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno IN emp.deptno%TYPE,
p_recordset OUT Types.cursor_type) AS
BEGIN
OPEN p_recordset FOR
SELECT ename, empno, deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/

The resulting cursor can be referenced from PL/SQL as follows:

SET SERVEROUTPUT ON 1000000
DECLARE
v_cursor Types.cursor_type;
v_ename emp.ename%TYPE;
v_empno emp.empno%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
GetEmpRS (p_deptno => 30,
p_recordset => v_cursor);

LOOP
FETCH v_cursor
INTO v_ename, v_empno, v_deptno;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);
END LOOP;
CLOSE v_cursor;
END;
/

In addition the cursor can be used as an ADO Recordset:

Dim conn, cmd, rs

Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"

Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn

cmd.CommandText = "GetEmpRS"
cmd.CommandType = 4 'adCmdStoredProc

Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)

cmd.Parameters.Append param1
param1.Value = 30

Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF
-- Do something
rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = nothing
Set param1 = nothing
Set cmd = nothing
Set conn = nothing