gjutras
asked on
ora-01036 illegal variable name/number. I can't see it can you?
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;
/
OracleParameter[] myParameters = new OracleParameter[] { new OracleParameter("SELECTEDR
XmlReader xRdr = oraclehelper.ExecuteXmlRea
the function is
public XmlReader ExecuteXmlReader(OracleCon
CommandType inCommandType,
string inCommandText,
params OracleParameter[] inCommandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand(inCommandTex
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
cmd.XmlQueryProperties.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;
/
Generally this error pops out when You have a number field compared to a non-number and though it seems not the case,check all Your =var to be sure they have the comparable variable types
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Closed, 500 points refunded.
modus_operandi
EE Moderator
modus_operandi
EE Moderator
select * from ods.region where description = descript;