Oracle PL\SQL stored procedures by default DO NOT return record sets!
I have learned that people with SQL Server experience expect this functionality, but Oracle historically did not provide this via PL\SQL. That may be because the standard Oracle client tools (Oracle Forms and Oracle Reports) built their own record sets internally - they did not depend on PL\SQL for this. In recent versions of Oracle, as Oracle has tried to make their database and software get along better with non-Oracle software, support for "ref cursors", arrays, collections. etc has been added to PL\SQL. But getting a "record set" out of a stored procedure may still be an easier task in SQL Server than in Oracle. Now, getting a stored procedure to perform efficiently and scale well to large numbers of users may be a much easier task in Oracle than in SQL Server.
Both systems have things they do very well, but be aware that they are very different systems, and their strengths and weaknesses are different.
--step 1
--Cretate the required package first
create or replace
PACKAGE UserRights AS
type t_cursor is ref cursor;
procedure RetriveList(cur_Result out t_cursor,val_MasterList VARCHAR2 );
procedure RetriveMaster(cur_Result out t_cursor,val_UserGroup in nvarchar2,val_MasterFileName in nvarchar2,val_CurrentAccessType in nvarchar2);
END UserRights;
--step 2
--Cretate the package body
create or replace PACKAGE BODY UserRights AS
PROCEDURE RetriveList(cur_Result out t_cursor,val_MasterList in VARCHAR2 ) AS
cMasterData VARCHAR2(50);
BEGIN
cMasterData:= upper(val_MasterList);
IF cMasterData = 'PRO' THEN
open cur_Result for SELECT GUPro REFID ,ProCode Code, Description Name FROM Protable WHERE upper(Status) = 'ACTIVE';
END IF;
IF cMasterData = 'ACCOUNT' THEN
open cur_Result for SELECT AccountCategoryID REFID, AccountCat Code, Name FROM AccountCategory;
END IF;
IF cMasterData = 'REPORTS' THEN
open cur_Result for SELECT REFID, Description Code, ReportName Name FROM Report;
END IF;
END RetriveList;
PROCEDURE RetriveMaster(
cur_Result out t_cursor
,val_UserGroup in nvarchar2
,val_MasterFileName in nvarchar2
,val_CurrentAccessType in nvarchar2) AS
cUserGroup VARCHAR2(50);
cMasterFileName VARCHAR2(50);
cCurrentAccessType VARCHAR2(50);
BEGIN
cUserGroup:= upper(val_UserGroup);
cMasterFileName:= upper(val_MasterFileName);
cCurrentAccessType:= upper(val_CurrentAccessType);
IF(cCurrentAccessType = 'ALL') THEN
IF cMasterFileName = 'TEST1' THEN
OPEN cur_Result FOR
SELECT PlanID , PlanCode Code , Description Name
FROM Promo;
END IF;
IF cMasterFileName ='TEST2' THEN
OPEN cur_Result FOR
SELECT AccountCategoryID GUID, AccountCategory , Name
FROM AccountCategory ;
END IF;
END IF;
END RetriveMaster;
END UserRights;
--step 3
-- Now run the required procedure
VARIABLE resultSet REFCURSOR
EXEC UserRights.RetriveList( :resultSet,'reports');
PRINT :resultSet
--
--c# 2003 code blog
public DataTable GetTheMaster()
{
string MasterFileName="Reports";
DataTable dtRMD=new DataTable();
OracleParameter []ParaList=new OracleParameter[2];
ParaList[0] = new OracleParameter("cur_Result",OracleDbType.RefCursor,ParameterDirection.Output );
ParaList[1] = new OracleParameter("MasterList",MasterFileName );
dtRMD= getDatasetSP("UserRights.RetriveList",ParaList).Tables[0];
return dtRMD;
}
public DataSet getDatasetSP(string strCommandText, OracleParameter []spList )
{
DataSet Results=new DataSet();
try
{
dbConn = new OracleConnection();
//conn.ConnectionString ="User ID=dbo_TestUser;Password=dbo_123abc;Data Source=abcoracle:1521/TestDB;Persist Security Info= true;";
dbConn.ConnectionString=connString;
//conn.ConnectionString = build.ToString();
dbConn.Open();
OracleCommand objCmd = new OracleCommand();
objCmd.Connection=dbConn ;//here your connection
objCmd.CommandText = strCommandText;
objCmd.CommandType = CommandType.StoredProcedure;
//objCmd.Parameters.Add( "cur_Result", OracleDbType.RefCursor,ParameterDirection.Output) ;
foreach(OracleParameter op in spList)
{
objCmd.Parameters.Add(op) ;
}
OracleDataAdapter da=new OracleDataAdapter(objCmd);
da.Fill(Results);
dbConn.Close();
}
catch(Exception ex)
{
dbConn.Close();
throw New Exception ("Unable to run the command. Reason." +ex.Message);
}
return Results;
}
for More infor for C# 2008 follow the link
http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx
