Error "ORA-00933: SQL command not properly ended" on Select with ODBC Command

Question

I'm trying to execute a query against an Oracle DB using ODBC in .NET and am getting the following error:

ORA-00933: SQL command not properly ended

However, the SQL statement is definitely correct, and I can execute it successfully from Oracle SQL Developer. The query itself looks like this:

SELECT * FROM TABLE(SCHEMA.PKG.SPNAME('PARAMS'));

Another simple query works fine:

SELECT COUNT(*) FROM SCHEMA.MYTABLE

It looks like something with the former, where it's using a package in the query and causing something to break. The error indicates that it's not properly ended, but it has a semi-colon and correct braces, so it seems something else is going on.

If I remove the trailing semi-colon, I get an error with no message.

My C# code is basic and looks like this:

using (var connection = new OdbcConnection(connectionString))
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = commandText;

        connection.Open();
        var result = command.ExecuteScalar();
        connection.Close();

        Console.WriteLine(result);
    }
}

When using the Oracle library for .NET, it works when I remove the trailing semi-colon. If I keep that in place, the same error about the SQL command not being properly ended comes up.

It seems like this query should work with ODBC. Is there anything I need to differently to get it working, or is using the Oracle Managed Data Provider the only way?

Answer 1

From my experience, I have noticed several instances where a semicolon will break the query, such as JasperSoft Studio and the cx_Oracle Python module. I know very little about ODBC vs OracleDataClient, but I would imagine this is a similar situation.