ORA-00904 error on good select

[Guest]

Hi;

We have a select that does work on Oracle, but does not work when going
through the OracleConnection ADO.NET connector. One column name is 28 chars
long and the other is 30 so they should be ok.

Does the connector count table + column name length when we do table.column
in the select?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Steven Cheng[MSFT]]

Hi Dave,

From your description, you're encountering some problem when try execute a
SELECT statment against ORACLE db in ADO.NET, correct?

For the case, would you provide some further info on this? For example,
whether you're using the .NET built-in provider or ORACLE provided one. And
for the problem table, what's the database schema/structure and what the
complete select statement look like. In addition, if possible, you can
perform the test against a simpler table and compare the difference.

Please feel free to post here if there is anything else I missed.

Sincerely,

Steven Cheng


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Paul Clement]

¤ Hi;
¤
¤ We have a select that does work on Oracle, but does not work when going
¤ through the OracleConnection ADO.NET connector. One column name is 28 chars
¤ long and the other is 30 so they should be ok.
¤
¤ Does the connector count table + column name length when we do table.column
¤ in the select?

You may want to post your SQL statement. The ORA error indicates there is a problem with a column
name or identifier.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

We are using the MS, not the Oracle connector. Here are the selects that are
failing:

select peerReviewMeetingDurationHrs from reviewcustom

select distinct softwareBuildRevisionOrVersion from reviewcustom

select distinct "Action Items & Disposition" from reviewcustom

The third one causes a prompt which doesn't work as it's an ADO.NET call.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Miha Markic]

We are using the MS, not the Oracle connector. Here are the selects that
are
failing:

select peerReviewMeetingDurationHrs from reviewcustom

select distinct softwareBuildRevisionOrVersion from reviewcustom

select distinct "Action Items & Disposition" from reviewcustom

The third one causes a prompt which doesn't work as it's an ADO.NET call.

I'd assume quotes are causing problems. Did you try using single quotes or
square brackets instead?

 

[Guest]

Good catch - the [] solved the third one. The first 2 still no go...

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[WenYuan Wang [MSFT]]

Hello Dave,

Your first 2 select query string seems fine.
I tried it on my Oracle Express 10g machine.

I created the table as below
create table reviewcustom(
softwareBuildRevisionOrVersion Number(8,2),
peerReviewMeetingDurationHrs Number(8,2)
)

Execute the query in .net application as below
System.Data.OracleClient.OracleConnection oc = new
System.Data.OracleClient.OracleConnection();
System.Data.OracleClient.OracleCommand ocd = new
System.Data.OracleClient.OracleCommand();
ocd.Connection = oc;
ocd.CommandText = @"select peerReviewMeetingDurationHrs from reviewcustom";
// I also tried another query
//ocd.CommandText = @"select distinct softwareBuildRevisionOrVersion from
reviewcustom";
int flg=ocd.ExecuteNonQuery();
Console.WriteLine(flg);
Console.Read();

It works fine. I failed to reproduce the issue. Would you please try the
above method and let me know the result? Does it work fine on your side? I
suspect the column name is not the root accuse of the issue. Is it possible
for you to send me the database files? I will try to repro the issue again.
My alias is (e-mail address removed)

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Miha Markic]

WenYuan,

I suggest you to obfuscate your e-mail address (they have to be readable to
human though). It is enough if you inject some obvious words, such as
NOSPAM, HATESPAM, etc.
Unless you want spam, of course ;-)

 

[Guest]

Miha - totally off-topic question for you. Do you know of a website where I
can buy Slovenian pop music CDs for delivery to the US? And if possible pop
music from other countries in the Balkans?

I'm a big Eurovision fan and you cannot find non-American artists here in
the US.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

WenYuan,

I suggest you to obfuscate your e-mail address (they have to be readable to
human though). It is enough if you inject some obvious words, such as
NOSPAM, HATESPAM, etc.
Unless you want spam, of course ;-)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hello Dave,

Your first 2 select query string seems fine.
I tried it on my Oracle Express 10g machine.

I created the table as below
create table reviewcustom(
softwareBuildRevisionOrVersion Number(8,2),
peerReviewMeetingDurationHrs Number(8,2)
)

Execute the query in .net application as below
System.Data.OracleClient.OracleConnection oc = new
System.Data.OracleClient.OracleConnection();
System.Data.OracleClient.OracleCommand ocd = new
System.Data.OracleClient.OracleCommand();
ocd.Connection = oc;
ocd.CommandText = @"select peerReviewMeetingDurationHrs from
reviewcustom";
// I also tried another query
//ocd.CommandText = @"select distinct softwareBuildRevisionOrVersion from
reviewcustom";
int flg=ocd.ExecuteNonQuery();
Console.WriteLine(flg);
Console.Read();

It works fine. I failed to reproduce the issue. Would you please try the
above method and let me know the result? Does it work fine on your side? I
suspect the column name is not the root accuse of the issue. Is it
possible
for you to send me the database files? I will try to repro the issue
again.
My alias is (e-mail address removed)

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Guest]

I copied and tried this (sorry, I should have done this first) and it works.
Ok, we'll dive into our DB and figure out what is going on. Oracle is
definitely NOT my favorite...

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Miha Markic]

You might have permission problems...

 

[WenYuan Wang [MSFT]]

Thanks for your advice, Miha.
I will obfuscate it next time.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello Dave,
Thanks for your reply.

I'm standing by. If there is anything we can help with, please feel free to
let me know.
We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

The DBA did something and now it's fixed.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[WenYuan Wang [MSFT]]

Thanks for your reply, Dave.
It's really great news the issue has been resolved.


Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.