SQL Query and Oracle => Error: "Data type is not supported"

thanks, but i'm not sure what you are saying there?

remember, i already have two identical queries, but selecting different tables which work fine. They have the format that i have mentioned. What is the error that is returned?

the error is about invalid database name of table name in this case either BIC or ACE_Data are not correct

humm, no this doesn't seem to be the problem. I tried changing the name with one of the select queries that works to see what kind of an error an invalid table name throws up, but the error in this case is 'ORA-00942: table or view does not exist'

So, that is not the error I'm getting. The table definitely exists then, I can confirm that now and that the name is correct... very strange. I mean, I think my code is probably ok, so I can only guess that there is some problem with the oracle client perhaps... maybe some kind of incompatability with the server database? I've no idea really i'm just guessing but i can't see any error in the code

Can you share your connection string?

Try selecting columns one by one field i.e.

SELECT FIELD1 FROM BIC.ACE_DATA

it will give you which column of table is giving error

What are the version of Oracle Client and Oracle Server ?

Please, use sqlplus (or any other tool that support the command desc) and send me the result of :

desc BIC.ACE_Data;

Deivid

yeah good idea...

humm, tried to do "SELECT ICO1 FROM BIC.ACE_Data" and got the error 'ORA-00904: "ICO1": invalid identifier'.

Tried with a different columnname: "SELECT 'Customer Name' FROM BIC.ACE_Data" and it worked actually, except that when i look at what was returned... there are 15,000 rows which all say 'Customer Name' ??? at least that's what comes out when i do a CopyFromRecordset(rs) into excel to see what's in the recordset

I'm not sure of the versions at the moment. I will have to check tomorrow and let you know if that's ok. What does the command desc do?

I'm not totally sure what you mean by use sql plus. At the moment I am using the code below in .net vs 2005, but not too familiar with accessing oracle databases. Until now i've  just been working with accessing access databases

'web.config
'connection string
<add key="BICServer" value="Provider=MSDAORA;Data Source=bicsite.world;User Id=AIDEN;Password=xxxx; Min Pool Size=10;Connection Lifetime=120;Connection Timeout=120;Incr Pool Size=5;Decr Pool Size=2"/>
 
'Then in my forms codebehind
Dim strBICServer As String = ConfigurationManager.AppSettings("BICServer")
      
strBICConnection = strBICServer
 
      If cnnBIC.State = 0 Then cnnBIC.Open(strBICConnection)
 
      'Opening a new recordset in this case
      rs.ActiveConnection = cnnBIC
 
MySQL = "SELECT * FROM BIC.ACE_Data"
rs.Open(MySQL)
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

http://msdn.microsoft.com/en-us/library/yk72thhd.aspx

Hi,

Well it seems like i shouldn't have a problem because apparently the Oracle server I am accessing is 9i and the client on my machine is also 9i... so i guess that's why the other queries work fine. So i'm not really sure what the problem is...

just searching the web... i found another person with this exact problem. the way they solved it was to use oraclecommand.... but it seems like i can't add this to my website... only for win apps... so it doesn't appear in my toolbox when i add it to my website. Is there anyway to use this... or a better way to attach to an oracle database from a website then using the oledb connection which seems a bit dodgy

Try upgrading the Oracle client to 10g, along with the .NET data provider for 10g. 10g client will work against a 9i database, and the client is much more mature and bug-free.

Ah, MJC beat me to saying update your client. :)

When you tried this query: "SELECT 'Customer Name' FROM BIC.ACE_Data", the result was exactly what I expect Oracle to give you.  That is, Oracle will give you as many rows in the result set as there are records in the table, and since you asked Oracle to give you exactly 'Customer Name', that is exactly what it will give you on each row.  That is not the syntax to use in Oracle if what you really want is the contents of a column named: customer_name.

When you connect to Oracle from an applcation that uses ODBC or .net, you have to be careful of two things:
1. Case sensitivity of table/column names (or not)
2. Fully-qualified object names.
 
By default, Oracle table and column names are not case-sensitive (and they do not contains spaces, but they may contain the underscore character between words).  They are usually all upper-case values, and as long as you use Oracle tools, you can refer to tables and/or columns in Oracle in any case, or mixed case and you do *NOT* need to use double quotes aorund Oracle table or column names then.  A problem comes with Microsoft tools for connecting to databases, since they tend to be case-sensitive for table and column names, and they tend to use double quotes around table and/or column names.  When Oracle sees double quotes around table or column names, the case of the name in the quotes must exactly match the value in the Oracle database, or Oracle will return the 'ORA-00942: table or view does not exist' error if it thinks the table name you provided is not valid, or the 'ORA-00904: ... invalid identifier' if it thinks the column name you provided is not valid.

 In Oracle, a fully-qualified table name includes the owner (also called a "schema" in Oracle) of the table, plus a dot (a period character) followed by the table name itself.  So, "BIC.ACE_Data" could be a valid, fully-qualified reference to a table in Oracle, assuming that the owner (schema) name is: "BIC" and that the table_name is: "ACE_DATA".  You don't always need to use that full [schema].[table_name] syntax in Oracle however.  If you log in to Oracle as the schema owner ("BIC" in this case) you never need to use the "BIC." prefix for objects that "BIC" owns.  Or, if an Oracle DBA created a public synonym named "ACE_DATA" for the table: "ACE_DATA" that is owned by "BIC", then no matter which Oracle username you use when you log in, you can simply use "ACE_DATA" to refer to the table.

Also, if you do not log in to Oracle as the table owner ("BIC") you could get the 'ORA-00904: table or view does not exist error' if BIC has not granted permission on the ACE_DATA table to the username that you do log in to Oracle with (or to a role that your username has).

Good news, Aiden.

You are very welcome.  Just glad to be of some assistance in the matter.  

Best regards and happy coding,

Kevin

markgeer: was that meant for a different question.  If so, I can delete this comment from here and then you can repost in the correct question.

thanks very much for that, unfortunately i closed the question already, but the information you give is very useful so i appreciate that.

sorry about that, thanks again.

you can leave comment there... it is still useful to look back on later, thanks