create or replace package mspetshop4.Package_CATEGORY
is
type mytype is ref cursor;
procedure Proc_GetData(myinput out mytype);
end; 

CREATE OR REPLACE package BODY "MSPETSHOP4".Package_CATEGORY
is
procedure Proc_GetData(myinput out mytype)
is
begin
open mycs for select * from "MSPETSHOP4"."CATEGORY";
end Proc_GetData;
end;

using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OracleClient;

namespace WebApplication3 {
    public class OracleProcedures {
        public static OracleConnection GetOracleConnection() {
            OracleConnection conn = new OracleConnection("Data Source=***;user id=MSPETSHOP4;password=***;min pool size=4;max pool size=4;");
            conn.Open();
            return conn;
        }

        public static void ExcuteProceduresDemo() {
            using (OracleConnection conn = GetOracleConnection()) {
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "MSPETSHOP4.Package_CATEGORY.Proc_GetData";
                OracleParameter para = new OracleParameter();
                para.ParameterName = "myinput";
                para.Direction = System.Data.ParameterDirection.Output;
                para.OracleType = OracleType.Cursor;
                cmd.Parameters.Add(para);
                str
                OracleDataReader dr =  cmd.ExecuteReader();
                while (dr.Read()) {
                    //code
                }
                dr.Close();
            }
        }
    }
}