Returning a result set from Oracle to .Net
Firstly, let me say how alien and difficult it is to change from SQL Server to Oracle. Secondly, if you are going to use Oracle, I definately recommend using it only in Toad for Oracle. It has built-in version control and intellisense.
Now to the issue at hand: Making an oracle stored procedure return a result set, and in my case, not just one result set, but multiple result sets. Unlike SQL Server, you can’t just SELECT * FROM …. and expect it to go to your standard out. Instead you have to SELECT into a “reference cursor”.
At my work, we are using a generic return cursor, specified as:
CREATE OR REPLACE PACKAGE PMCS."EPB_PKGGENERIC" AS
poreq epb_distributor.po_reqd%TYPE;
type rc_genericresultset IS ref CURSOR;
type l_prodqty IS TABLE OF NUMBER INDEX BY binary_integer;
lprdqty l_prodqty;
lprdsumqty l_prodqty;
END epb_pkggeneric;
Then we use this as an output parameter in our stored procedure:
CREATE OR REPLACE PROCEDURE PMCS."PMCS_GENERATE_ACNT_REPORT"
(
p_retail_stores_result OUT PMCS.Epb_Pkggeneric.RC_GENERICRESULTSET
)
AS
BEGIN
OPEN p_retail_stores_result FOR
SELECT
RP.TERRITORY_NBR,
RP.UNIT_CODE,
RP.PHYSICAL_DISTRICT,
RP.RTL_ACCOUNT_NBR,
RP.STORE_NAME,
RP.CITY_NAME,
RP.STATE_CODE,
RP.ZIP_CODE,
RP.RTL_LEADER_LEVEL,
RP.PROMO_SUPP_NO,
RP.PROMO_SUPP_NAME,
RP.PRI_SUPPLIER_NO,
RP.PRI_SUPPLIER_NAME
FROM
PMCS.RTL_PROFILE RP;
END;
Finally in C# we can consume this stored procedure with:
.. create connection (conn) here, then ..
OracleCommand cmd = new
OracleCommand(string.Format("PMCS.PMCS_GENERATE_ACNT_REPORT",
_schemaName), conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter retailStoresResultParam =
cmd.Parameters.Add(new OracleParameter("p_retail_stores_result",
OracleDbType.RefCursor, System.Data.ParameterDirection.Output));
// load all output RefCursors into a dataset
DataSet ds = new DataSet();
new OracleDataAdapter(cmd).Fill(ds);
... close connection here ...
This will load each output parameter that is of type OracleDbType.RefCursor into the DataSet “ds”. Each output parameter will be a different anonymous table within the Tables collection.
