There was a user in MSDN forums having problems executing a valid Sql Plus command using ADO.Net. It seems that the OracleCommand object that he's using is generating an error upon execution of a DESC statement.
First of all, lets just take a look on what the "DESC" statement is. A DESC statment is an Oracle SqlPlus command that lists information on an oracle object (view, table, etc...). This is used as "DESC <objectname>", the object name there is a parameter that asks for any object in your oracle database.
The user is executing the DESC command through an OracleCommand object this way:
oraCommand = new OracleCommand("desc customer", myConnection);
dbReader = oraCommand.ExecuteReader(); |
As you can see it's a pretty quite valid expression. And since he tried executing the DESC command manually against the oracle database, he then assumed that this would work. As so did I. But I guess we're both wrong. This generates an error indicating that a wrong Sql-Syntax have been sent to the server.
I could only assume that this error is being generated for the fact that the DESC command isn't a valid SELECT, UPDATE or DELETE command. So another approach must be undertaken. Since Oracle PL/SQL is not much different from SQLServer's T-SQL I tried manually getting the object's information through its DataDictionary same as using the sysobject table in SqlServer. Which brought me to an alternative SELECT statement to query the results in the DESC command. Here it is:
| SELECT column_name "Name", nullable "Null?", concat(concat(concat(data_type,'('),data_length),')') "Type" FROM user_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE'; |
I just fed this SELECT statement into an OracleCommand object and voila! It worked! Here's the final code generated:
oraCommand = new OracleCommand("SELECT column_name "Name", nullable "Null?", concat(concat(concat(data_type,'('),data_length),')') "Type" FROM user_tab_columns WHERE table_name='customer';", myConnection);
dbReader = oraCommand.ExecuteReader(); |
And also here's the link to the original MSDN question.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=842818&SiteID=1