I'm getting an oracle exceptioin while running the following.
OracleParameter[] myParameters = new OracleParameter[] { new OracleParameter("SELECTEDR
EGIONS", OracleDbType.RefCursor, ParameterDirection.Output)
};
XmlReader xRdr = oraclehelper.ExecuteXmlRea
der(myConn
ection,Com
mandType.S
toredProce
dure,"TEST
_TESTPACKA
GE.TEST_GE
TREGIONS_S
P",myParam
eters);
the function is
public XmlReader ExecuteXmlReader(OracleCon
nection inConnection,
CommandType inCommandType,
string inCommandText,
params OracleParameter[] inCommandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand(inCommandTex
t,inConnec
tion);
cmd.CommandType = inCommandType;
if (inCommandParameters != null)
{
foreach (OracleParameter op in inCommandParameters)
{
cmd.Parameters.Add(op);
}
}
cmd.BindByName = true;
cmd.XmlCommandType = OracleXmlCommandType.Query
;
cmd.XmlQueryProperties.Roo
tTag = "Result";
cmd.XmlQueryProperties.Row
Tag = "Row";
if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open();
XmlReader retval = null;
//create the DataAdapter & DataSet
retval = cmd.ExecuteXmlReader();
// detach the OracleParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}
The stored proc is:
CREATE OR REPLACE package GJUTRAS.test_TestPackage AS
type sregions is REF CURSOR;
procedure test_GetRegions_sp
(
selectedregions out sregions
);
procedure test_GetRegByDescrip_sp
(
descript in varchar2,
selectedregions out sregions
);
end test_TestPackage;
/
CREATE OR REPLACE package body GJUTRAS.test_TestPackage AS
procedure test_GetRegions_sp
(
selectedregions out sregions
)
is
begin
open selectedregions for
select * from ods.region;
end test_GetRegions_sp;
procedure test_GetRegByDescrip_sp
(
descript in varchar2,
selectedregions out sregions
)
is
begin
open selectedregions for
select * from ods.region where description = descript;
end test_GetRegByDescrip_sp;
end test_TestPackage;
/
Start Free Trial