How to return resultset from stored procedure In ORACLE

Hey some people thinking about its simple retreive data from  query rather than stored procedure.
But is more simple and safe to so do changes if required.

For that we need to follow steps as follows :
[Note : Creating stored proc. and package only through sqlplus]
1)   We need to package of oracle to create it.

Packages in oracle syntax :
 
   CREATE OR REPLACE PACKAGE SELECT_EMP_HISTORY AS
    TYPE T_CURSOR IS REF CURSOR;
    PROCEDURE Sp_Test_Emp_Data
    (
            p_year IN VARCHAR2,
           cur_JobHistory OUT T_CURSOR
    );
    END SELECT_EMP_HISTORY;
   /


 Create  Package Body :

    CREATE OR REPLACE PACKAGE BODY SELECT_EMP_HISTORY AS
     PROCEDURE Sp_Test_Emp_Data
    (
       p_year IN VARCHAR2,
       cur_JobHistory OUT T_CURSOR
     )
        IS
        BEGIN
       OPEN cur_JobHistory FOR
       SELECT * FROM Table1  WHERE num=@p_year ;
       END Sp_Test_It_Projection_Data;
      END SELECT_EMP_HISTORY;
     /

[ Your Stored Procedure over view created , now you can edit your procedure according to requierment ]

C# :
         // Open Oracle connection and "conn" is connection variable in sample prog.
           OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT_EMP_HISTORY.Sp_Test_It_Projection_Data";
            cmd.CommandType = CommandType.StoredProcedure;
            // add the parameters for the stored procedure including the REF CURSOR
            // to retrieve the result set
            cmd.Parameters.Add("p_finYear", OracleDbType.Varchar2).Value = "100";
            cmd.Parameters.Add("cur_JobHistory", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            // open the connection and create the DataReader
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();
            // output the results and close the connection.
            DataTable dt = new DataTable();
            dt.Load(dr);