| Groups | Blog | Home |
dotnet ado.net : ORA-00904 error on good select
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
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. -------------------- [quoted text, click to view] >From: =?Utf-8?B?RGF2aWQgVGhpZWxlbg==?= <thielen@nospam.nospam>
>Subject: ORA-00904 error on good select >Date: Sun, 30 Sep 2007 21:16:01 -0700 >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 > > >
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 [quoted text, click to view] "Steven Cheng[MSFT]" wrote:
> 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. > > > -------------------- > > >From: =?Utf-8?B?RGF2aWQgVGhpZWxlbg==?= <thielen@nospam.nospam> > >Subject: ORA-00904 error on good select > >Date: Sun, 30 Sep 2007 21:16:01 -0700 > > >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 > > > > > > >
[quoted text, click to view]
On Sun, 30 Sep 2007 21:16:01 -0700, David Thielen <thielen@nospam.nospam> wrote: ¤ 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 ~~~~
[quoted text, click to view] "David Thielen" <thielen@nospam.nospam> wrote in message news:8C6845BE-7A1C-4255-8ADC-F9625CC3D3B5@microsoft.com... > 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? -- 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/
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 [quoted text, click to view] "Miha Markic" wrote:
> > "David Thielen" <thielen@nospam.nospam> wrote in message > news:8C6845BE-7A1C-4255-8ADC-F9625CC3D3B5@microsoft.com... > > 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? > > -- > 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 v-wywang@microsoft.com 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 - 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 [quoted text, click to view] "Miha Markic" wrote:
> 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/ > > "WenYuan Wang [MSFT]" <v-wywang@online.microsoft.com> wrote in message > news:71xWHrZBIHA.6080@TK2MSFTNGHUB02.phx.gbl... > > 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 v-wywang@microsoft.com > > > > 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. > > >
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 [quoted text, click to view] "WenYuan Wang [MSFT]" wrote:
> 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 v-wywang@microsoft.com > > 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,
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/ [quoted text, click to view] "WenYuan Wang [MSFT]" <v-wywang@online.microsoft.com> wrote in message
news:71xWHrZBIHA.6080@TK2MSFTNGHUB02.phx.gbl... > 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 v-wywang@microsoft.com > > 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. >
You might have permission problems...
-- 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/ [quoted text, click to view] "David Thielen" <thielen@nospam.nospam> wrote in message
news:3324DAD6-1D29-4D46-8911-DA2D404315F7@microsoft.com... >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 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.
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 [quoted text, click to view] "WenYuan Wang [MSFT]" wrote:
> 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. >
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.
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.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |