Oracle ORA-00904: database, table, column invalid identifier

I used Subsonic to generate a DAL for a couple of tables of a preexisting Oracle database. The subcommander did its thing but when I try to perform a query I get the following error.

Oracle ORA-00904: CDS, DAT, DAT_DBR_NO invalid identifier

 Where database, table, column are the names of my database table and column.

Dim query As Query

query = New Query(Tables.Dat).andWhere(Dat.columns.Dat_Dbr_No, "123123").ExecuteReader

 The strange thing is I know everything is correct because Subsonic created the DAL.

After googling around I found something about if a copy of the table exists, but is missing the column, that might be related. However, the third party database does not appear to have a duplicate table. Also, the Oracle is 9i.

 Any ideas for this would be appreciated.

 Sincerely,

I forgot to mention that the connection string and subsonic service look like the following:

<connectionStrings>
<add name="OracleConnection" connectionString="Data Source=oracledb.mydomain.com;User Id=username;Password=password;" providerName="System.Data.OracleClient"/>
</connectionStrings>
<SubSonicService defaultProvider="OracleProvider" enableTrace="true" templateDirectory="">
<providers>
<clear/>
<add name="OracleProvider" type="SubSonic.OracleDataProvider, SubSonic" connectionStringName="OracleConnection" fixDatabaseObjectCasing="false" fixPluralClassNames="false" generatedNamespace="DAL.DATABASE" generateNullableProperties="false" generateODSControllers="false" includeTableList="TABLE1,TABLE2" removeUnderscores="false" useSPs="false"/>
</providers>
</SubSonicService>

Could be related to the case sensitive of tablenames?

Thanks for the suggestion. The tables are all caps, so I don't know if that is the issue, but I will certainly double check. Also, we are upgrading from 9.2 to 10g this weekend.I will give it another go and see if that changes anything.

 I think I may have found the problem.   I was getting the exact same Error in Oracle.  The following Code

PersonCollection pc = new PersonCollection().Where("First_Name", "bret").Load();

would give me the error as it generated the Following SQL

SELECT PERSON.PERSON_ID, PERSON.FIRST_NAME, PERSON.LAST_NAME, PERSON.DOB FROM PERSON WHERE TESTER.PERSON.First_Name = :First_Name0 ORDER BY PERSON_ID ASC

(Tester is the schema that I am using in Oracle)  

The SQL should be the following

SELECT PERSON.PERSON_ID, PERSON.FIRST_NAME, PERSON.LAST_NAME, PERSON.DOB FROM TESTER.PERSON WHERE TESTER.PERSON.First_Name = :First_Name0 ORDER BY PERSON_ID ASC

The Schema is not being applied in the From Clause but is being applied in the Where which for some reason Oracle (10gR2) doesn't like

 I think I may have found the fix

Change line 808 of the Oracle Data Provider to the following

query = select + columns +

" FROM " + table.ToString() + where + order;

Let me know if this fixes your error.  If so we should submit this as a change request.

 Great I can't wait to try it out.

 You're the man. That did it.

I am having the same problem.  But, I am VERY new to SubSonic, and I don't understand how to apply the above fix.  Where/what is "line 808 of the Oracle Data Provider"?  Thanks!

If you download the src to Subsonic 2.1 Final there is a file named OracleDataProvider.cs once you find the class you will see what I am talking about.  You just need to replace the line and recompile Subsonic and use the newly compiled Subsonic dll.

THANK YOU!  THANK YOU!  THANK YOU!

I also saw your review on the CodePlex/SubSonic Releases page:

"If you intend on using Subsonic with Oracle however continue to use Subsonic 2.0.3 as I have ran into a lot of issues using Subsonic 2.1 with the OracleDataProvider"

and was thinking about trying to get the older version.  But I saw your reply in time, tried it, and it works!

Thanks - this got me over that hump.  

Now, while using FetchById I am now getting an "Invalid attempt to Read when reader is closed" InvalidOperationException in ReadOnlyRecord line 192.  The reader is closed by OracleDataProvider line 245 call to Dispose on the AutomaticConnectionScope which seems a little redundant since ReadOnlyRecord closes the reader itself.  Is the Dispose call always needed or can we just do this in the case of an OracleException?

Yeah I ran into the same problem.  The OracleData Provider has two large issues.   The schema error and the closing of the connection.  You need to mimick the MySQL Data Provider, If you look at the comments in the MySQL Data Provider it looks like it was having a similar error but they fixed it in the MySQL Data Provider and not the rest of the providers.   I submitted a patch that fixes both of the errors mentioned above and that automatically returns the ID of a newly selected record if it has a primary key of type number.   Check the patch out as I also changed my fix for the FetchById error so that it is less of a hack.

http://www.codeplex.com/subsonic/WorkItem/View.aspx?WorkItemId=20020