asked on

Getting ORA-00911:invalid character when using "ORDER BY" in dynamic SQL statement using Oracle.DataAccess

Hi:

I'm running into some issues using the Oracle.DataAcess.Client.OracleAdapter.
If my dynamic sql contains an "ORDER BY" clause, then I get the following exception:
ORA-00911:invalid character

Here is a coding snippet outlining the issue:
-------------------------------------------------------
using (OracleConnection oraconn = new OracleConnection(dataConn))
{
oraconn.Open();

Oracle.DataAccess.Client.OracleCommand cmd = new OracleCommand(sOraSQL, oraconn);
cmd.CommandType = CommandType.Text;
Oracle.DataAccess.Client.OracleDataAdapter da = new OracleDataAdapter(cmd);

DataTable dt = new DataTable();
da.Fill(dt);

oraconn.Close();
return dt;
}

If I use the following SQL string, it works fine.
----------------------------------------------------------
String sOraSQL = "SELECT a, b from table_foo;"

If I add an "ORDER BY" clause, I get the exception
----------------------------------------------------------
String sOraSQL = "SELECT a, b from table_foo ORDER BY a ASC;"

I'm using the following reference in my solution:
------------------------------------------------------------
Oracle.DataAccess
Runtime Version V1.0.3705
Version 10.2.0.100

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndyAinscow

Without the trailing ';' in the statement - does that cure it?

Naveen Kumar

Can you try this :

String sOraSQL = "SELECT a, b from table_foo ORDER BY 1 ;"

--> no need of ASC keyword because default is ASC only

If it still does not work, can you try it without the semicolon at the end.

String sOraSQL = "SELECT a, b from table_foo ORDER BY 1"

Thanks

Guy Hengel [angelIII / a3]

I also must presume that the ";" in the sql should be removed ...

Naveen Kumar

But surprising thing is the below from question author.

If I use the following SQL string, it works fine.
----------------------------------------------------------
String sOraSQL = "SELECT a, b from table_foo;"

--> ; is still present in the above as well. I can think of a typo while typing here or something like that.

AndyAinscow

One other thought which might not be relevant.
I assume a is a placeholder but what is the actual name? Maybe it requires square braces because it contains an invalid character (though why it is OK in the SELECT part is beyond me - unless it doesn't actually appear in the select part)

ie.
String sOraSQL = "SELECT a, b from table_foo ORDER BY [Column One] ASC;"

because
String sOraSQL = "SELECT a, b from table_foo ORDER BY Column One ASC;"
is giving it problems

Naveen Kumar

Got something of this sort in mind and hence suggested to use "order by 1" as posted in my previous updates.

thanks,

Getting ORA-00911:invalid character when using &quot;ORDER BY&quot; in dynamic SQL statement using Oracle.DataAccess

Getting ORA-00911:invalid character when using "ORDER BY" in dynamic SQL statement using Oracle.DataAccess