• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2227
  • Last Modified:

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;
/
0
gjutras
Asked:
gjutras
  • 2
1 Solution
 
oleggoldCommented:
You should check the following:
  select * from ods.region where description = descript;
0
 
oleggoldCommented:
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
0
 
gjutrasAuthor Commented:
in case anybody is looking at this for a solution,
The answer is:
On the 10g version of odac components, you can not
output a ref cursor from a stored proc to .net.

You can do it from sql within .net, but not from a stored proc.
0
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
EE Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now