Link to home
Start Free TrialLog in
Avatar of gjutras
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("SELECTEDREGIONS", OracleDbType.RefCursor, ParameterDirection.Output) };
XmlReader xRdr = oraclehelper.ExecuteXmlReader(myConnection,CommandType.StoredProcedure,"TEST_TESTPACKAGE.TEST_GETREGIONS_SP",myParameters);
the function is
public XmlReader ExecuteXmlReader(OracleConnection inConnection,
            CommandType inCommandType,
            string inCommandText,
            params OracleParameter[] inCommandParameters)
        {
            //create a command and prepare it for execution
            OracleCommand cmd = new OracleCommand(inCommandText,inConnection);
            cmd.CommandType = inCommandType;
            if (inCommandParameters != null)
            {
                foreach (OracleParameter op in inCommandParameters)
                {
                    cmd.Parameters.Add(op);
                }
            }
            cmd.BindByName = true;
            cmd.XmlCommandType = OracleXmlCommandType.Query;
            cmd.XmlQueryProperties.RootTag = "Result";
            cmd.XmlQueryProperties.RowTag = "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;
/
Avatar of oleggold
oleggold
Flag of United States of America image

You should check the following:
  select * from ods.region where description = descript;
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
Avatar of gjutras
gjutras

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of modus_operandi
modus_operandi

Closed, 500 points refunded.
modus_operandi
EE Moderator