Returning Oracle SELECT query results to DataSet
I am encountering a strange problem when trying to return the results of a simple Oracle SELECT query to an ADO.NET recordset. I can do this without problems with a stored procedure, it is only when I use a "hard-coded" SELECT statement that I get issues. I need to do this because some of my SQL is dynamically generated.
Example code is as follows:-
sql = "SELECT * FROM TBLSUBJECTS;";
oraCmd.CommandType = CommandType.Text;
oraCmd.CommandText = sql;
ds = new DataSet();
oraDa = new OracleDataAdapter(oraCmd);
...when the .Fill() method executes, I get the following error returned from Oracle:-
"ORA-01036: illegal variable name/number"
Can anyone tell me what I am doing wrong? Do I need to declare a RefCursor and return the results through that? I am using Oracle 9i.