OraOLEDB.Oracle Provier and sub-select problem

OraOLEDB.Oracle seems not supporting subselct, is there a way to walk around it?
select id,(select 1 from dual) as abc from table;
this give "select1fromdual: invalid identifier" error when I use OraOLEDB.Oracle provider, but it works using MSDAORA.1 provider, however there's several other reasons i have to use OraOLEDB.Oracle, anyway to solve this?
P.S. I don't like the sub-select query, but there's too much job to do to change all queries with sub-select in the application
dragonempAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrSQLCommented:
dragonemp,
   Could you provide some more detail about what you are doing?  Is the "table" in your query an Oracle table?  What tool are you calling the OLE from?  And how do you make the Oracle connection?  Finally, what version of Oracle are you using?

Good luck!
DrSQL
dragonempAuthor Commented:
It's a c# web application against Oracle 9i database. Like I said, I tested 2 connection string, 1 with proiver "OraOLEDB.Oracle", 1 with "MSDAORA.1", Oracle one doesn't support sub-select but m$ one works.
slightwv (䄆 Netminder) Commented:
What version of 9i?  Please provide all 4 levels.  If you are running 9.0.??  nothing would surprise me.  It's very buggy.

Also, can you provide a failing code stub?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

dragonempAuthor Commented:
The version is 9.2.0.1.0.
After establishing a connection with OraOLEDB.Oracle Provider, then run a query with sub-select will give error, it even doesn't matter which language I'm using, the error is come from provider, it just doesn't recognize the subselect, and think subselect is a invalid identifier.
the query is like this:
select id,(select 1 from dual) as abc from table;
DrSQLCommented:
dragonemp,
   We still need to know more about the connection string and how you prepare the select for execution.

Good luck!
DrSQL
dragonempAuthor Commented:
Like I said, it really doesn't matter which method I use in the code, as long as i use OraOLEDB provider, it will give error
Here's an asp sample of code I use to test
<%
Dim mUIConnection,conn,rs,strSQL
mUIConnection = "Provider=OraOLEDB.Oracle;User ID=user;Password=pass;Data Source=oracle;"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open mUIConnection
strSQL = "SELECT VLD_SITE_ID, (SELECT 1 FROM DUAL) as XYZ FROM VLD_SITE"
set rs = conn.Execute(strSQL)
if not rs.eof then
      response.write("ok"&rs(1))
else
      response.write("error")
end if
rs.close
set rs=nothing
conn.Close
set conn=nothing
%>

and the error:
OraOLEDB error "80040e14"
ORA-00904: "SELECT1FROMDUAL" : invalid identifier

DrSQLCommented:
dragonemp,
    I'm assuming that the name of your object is "oracle" and you did the create on it:

Data Source
A data source object in OraOLEDB is responsible for establishing the first connection to the Oracle database. To establish the initial connection, the consumer must use the CoCreateInstance function to create an instance of the data source object. This function requires important information about the provider: class ID of the provider and executable context. The class ID of OraOLEDB is CLSID_OraOLEDB.

OraOLEDB is an in-process server. When calling CoCreateInstance, use the CLSCTX_INPROC_SERVER macro. For example:

// create an instance of OraOLEDB data source object and
// obtain the IDBInitialize interface
hr = CoCreateInstance(CLSID_OraOLEDB, NULL,
                      CLSCTX_INPROC_SERVER, IID_IDBInitialize,
                     (void**)&pIDBInitialize);

or, you may need to declare the possibility of a multi-row return:
Conn.Properties ("PLSQLRSet") = TRUE  

And, you don't have your dim's specifically laid out.  Are you using this documentation?
http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95498/using.htm#1014442

Good luck!
DrSQL


slightwv (䄆 Netminder) Commented:
9.2.0.1 was pretty buggy.  I suggest patching to 9.2.0.6

I no longer have 9i around but your posted code worked in 10.1.0.4.

Just for grins try the following.  It's based on some very old OLEDB/ASP code I still have around.  It's basically the same and will probably fail as well.  I'm still thinking bug...
--------------------------------------------------------------------------------


<%@LANGUAGE="VBSCript"%>
<% Option Explicit
Response.Buffer = True

Dim Conn, Cmd, Rs
Set Conn = Server.CreateObject("ADODB.Connection")
Set Cmd = Server.CreateObject("ADODB.Command")
Set Rs = Server.CreateObject("ADODB.RecordSet")
Dim strConnection

'Use the correct SQL*NET connect string!
strConnection="Provider=OraOLEDB.Oracle;User ID=user;Password=pass;Data Source=oracle;"
Conn.Open strConnection

Cmd.ActiveConnection = Conn
Cmd.CommandText = "SELECT dummy, (SELECT 1 FROM DUAL) as XYZ FROM dual"

Set Rs = Cmd.Execute
if not rs.eof then
     response.write("ok"&rs(1))
else
     response.write("error")
end if

Set Rs = Nothing
Set Cmd = Nothing
Conn.Close
Set Conn = Nothing
%>
dragonempAuthor Commented:
The code is not the problem, the asp is a mere example to test. The real application I'm working on is not asp but c#. The error DOES come from provider itself not code. And it seems version dependant, I test application with 8i version provider, it works. So I'm asking if there's a way to work around it or if there's a patch available for this issue?
dragonempAuthor Commented:
yes, slightwv, I'm thinking it's bug as well now since I test it on old 8i version and it works......
DrSQLCommented:
dragonemp,

I found this:
6. I receive "Run-Time error:  '-2147217900 (80040e14)'" when trying to
   run a stored procedure; how can I overcome this error?

Answer
------
Ensure that you have set PLSQLRSet = 1 in the connect string.  The default
is 0, but it must be set to 1 to return a result set from a stored procedure.


And, you could try helping the database to KNOW there's only one record in your subselect:
Cmd.CommandText = "SELECT dummy, (SELECT max(1) FROM DUAL) as XYZ FROM dual"

I realize you're not calling a stored procedure, but it could be part of a bug that subselects are requiring it.

Good luck!
DrSQL
slightwv (䄆 Netminder) Commented:
I would tend to agree that the code should not be the problem.  Although I have seen many times where a slight difference in code gets around many of the bugs.

As for a patch, I would get the 9.2.0.6 patchset and update everything.  Again, 9.2.0.1 was VERY buggy and not all that stable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dragonempAuthor Commented:
Thanks for all your answers, I guess that's it for now, I will test 9.2.0.6 patchset.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.