Getting "Syntax Error: Expecting identifier or quoted identifier." exception when trying to use DataAdapter.FillSchema() on an Oracle Database

I am working an a Visual Studio 2008 C# application connecting to an Oracle 10.2 database using built in .NET data object for Oracle.

My code as exists looks like:

da.FillSchema(sourceDt, SchemaType.Mapped);



When I run this code, I get the following exception: "Syntax Error: Expecting identifier or quoted identifier."

The SQL that is being run is: select * from (SELECT * FROM company) OracleTestSQL

This select works correctly when run directly against the database when using a tool like DBVisualizer.

The code also works perfectly, without generating any exception when I change it to the following:

da.Fill(sourceDt);



Granted, I could just use that last bit of code, but for reasons of speed and latency, I want to only get the schema/data that I need, rather then alot of extra data.

Any help/suggestions would be greatly appreciated! Thanks in advance!

Aaron@Exago

StewartMeyersAsked:
Who is Participating?
 
StewartMeyersConnect With a Mentor Author Commented:
I found found the answer to the problem myself. The code works just fine if I use the Oracle provided .NET Data Adapter instead of using Microsoft's provided Data Adapter for Oracle.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
cna you please explain why you run that subquery?
or is that just a sample or what you really want to run?

listening...
0
 
StewartMeyersAuthor Commented:
That SQL sample was just a testing sample. It can be any possible sub query, but in my testing, I just happened to use a basic sub select in the overall select.

The application needs to be any type of sub select and or joins, etc and treat it as a data object.

Hopefully that helps to clarify.
 
 
0
 
RPCITCommented:
try explicitly defining the field names in the select...

select field1, field2, field3, field4, from (SELECT * FROM company) OracleTestSQL


The scheme mapping may not know how to deal with the wildcard field operator
0
 
StewartMeyersAuthor Commented:
Thanks RPCIT, but I wouldn't worry about the select *. It's standard SQL and works (and generates the proper rows/data) in any other SQL editor connecting to the same database using the same database connector (I'm using DBVisualizer). If the schema mapping didn't know how to deal with the wildcard, I would think it would also throw the error in any tool.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.