Issue with Managed Oracle Provider in .NET

[Pat]

Can any one clarify this issue with Managed Oracle Provider in .NET ?

I am trying to run a very simple SQL query using the ExecuteScalar()
method of the command object. I get an error stating: ORA-00911:
invalid character

--------------------Code------------------------
public Object fetchScalar( IDbConnection con, String strSql)
{
IDbCommand cmd = null;

try {

//Prepare the Command
cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = strSql;
cmd.CommandType = System.Data.CommandType.Text;

try {
cmd.CommandTimeout = 30;
Object result = cmd.ExecuteScalar();
return result;
}
finally {
if (null != cmd) cmd.Connection = null;
cmd = null;
}
}
catch( Exception e ) {
throw new Exception( ERR_CantFetchData, e );
}
}
-------------------End Code---------------------

--------------------SQL-------------------------
select B2.BUS_NAME_ID BusNameId from business_name B1,
business_name B2 where B1.BUS_NAME_ID = 2201110001 and
B1.BUS_NAME_TYPE_CD = 1004 and B1.ENTITY_ID = B2.ENTITY_ID and
B2.BUS_NAME_TYPE_CD = 1001;
-------------------End SQL----------------------

------------------Error Message-----------------
Error: ORA-00911: invalid character
Module: System.Data.OracleClient
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle
errorHandle, Int32 rc)

at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean isReader, Boolean
needRowid, OciHandle& rowidDescriptor, ArrayList&
refCursorParameterOrdinals)

at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean needRowid,
OciHandle& rowidDescriptor)

at System.Data.OracleClient.OracleCommand.ExecuteScalarInternal(Boolean
needCLStype, Boolean needRowid, OciHandle& rowidDescriptor)

at System.Data.OracleClient.OracleCommand.ExecuteScalar()

-----------------End Error----------------------

 

[David Browne]

Can any one clarify this issue with Managed Oracle Provider in .NET ?

I am trying to run a very simple SQL query using the ExecuteScalar()
method of the command object. I get an error stating: ORA-00911:
invalid character

--------------------Code------------------------
public Object fetchScalar( IDbConnection con, String strSql)
{
IDbCommand cmd = null;

try {

//Prepare the Command
cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = strSql;
cmd.CommandType = System.Data.CommandType.Text;

try {
cmd.CommandTimeout = 30;
Object result = cmd.ExecuteScalar();
return result;
}
finally {
if (null != cmd) cmd.Connection = null;
cmd = null;
}
}
catch( Exception e ) {
throw new Exception( ERR_CantFetchData, e );
}
}
-------------------End Code---------------------

--------------------SQL-------------------------
select B2.BUS_NAME_ID BusNameId from business_name B1,
business_name B2 where B1.BUS_NAME_ID = 2201110001 and
B1.BUS_NAME_TYPE_CD = 1004 and B1.ENTITY_ID = B2.ENTITY_ID and
B2.BUS_NAME_TYPE_CD = 1001;
-------------------End SQL----------------------

ORA-00911: invalid character means exactly what it says. Your query
contains an invalid character.

To wit : ';'

';' is invalid in SQL. It's interpreted a statement terminator in SQLPlus,
and is stripped from SQL statements before they are sent to Oracle. It's
also the statement terminator in PL/SQL. But it's invalid in SQL.

David

 

[Guest]

Not sure it will help, but the JOINing clause in the WHERE (when using WHERE
instead of JOIN syntax) is normally before filtering WHERE clauses, ala:

select B2.BUS_NAME_ID BusNameId from business_name B1,
business_name B2 where B1.ENTITY_ID = B2.ENTITY_ID and B1.BUS_NAME_ID
= 2201110001 and
B1.BUS_NAME_TYPE_CD = 1004 and
B2.BUS_NAME_TYPE_CD = 1001;

If this is 10g, the managed provider does not work correctly. You will have
to switch to ODP.NET. Some 9i features are marginal, as the OracleClient
provider was released pre 9i.

You can also try using the ANSI standard JOIN syntax:

select B2.BUS_NAME_ID BusNameId
from business_name B1
JOIN business_name B2
ON B1.ENTITY_ID = B2.ENTITY_ID
WHERE B1.BUS_NAME_ID = 2201110001 and
B1.BUS_NAME_TYPE_CD = 1004 and
B2.BUS_NAME_TYPE_CD = 1001;

I am not sure if the ; is needed in the coded statement, as it is
automagically added by the OracleClient provider, so that might be your
statement blow up.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[kriv jOHN]

Consider the issue closed. I left in the extra ; at the end. lol.

Thanks Gregory.