Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 245379 - Last Review: September 30, 2003 - Revision: 2.0
PRB: MS OLE DB Provider for Oracle Doesn't Support INNER JOIN Syntax with ODBC Escape Sequence
This article was previously published under Q245379
When an INNER JOIN operation is performed on two or more tables with the ODBC Escape Sequence "{oj}" using the Microsoft OLE DB provider for Oracle, a syntax error, access violation, or DB_E_ERRORSINCOMMAND may occur.
Oracle PL/SQL does not support this syntax. The Microsoft OLE DB Provider for Oracle is a native provider that uses the Oracle OCI API to translate and make calls to the Oracle database back end. It is not possible to perform joins on another table in Oracle using this syntax.
Use the Oracle Native Join syntax in the query instead of the ODBC "{oj}" escape sequence. For instance, the following INNER JOIN syntax can be resolved by the second statement:
SELECT * FROM { oj tab1 INNER JOIN tab2 ON tab1.col1=tab2.col2}
SELECT * FROM tab1, tab2 WHERE tab1.col1 = tab2.col2
This behavior is by design.
It is not possible to use the INNER JOIN syntax with Oracle tables even though the INNER JOIN syntax is compliant to SQL92/ANSI standards. This is a limitation of Oracle's PL/SQL language.
Steps to Reproduce Behavior
From the Oracle SQL*PLUS client tool, run the following commands:
CREATE TABLE tab1(col1 INT,col2 INT);
CREATE TABLE tab2(col1 INT,col2 INT);
Execute the following query using the RowsetViewer application that comes with the Platform SDK:
SELECT * FROM {oj tab1 INNER JOIN tab2 ON tab1.col1=tab2.col2}
The following error is returned:
Interface: IID_ICommand
Result: 0x80040e14 = DB_E_ERRORSINCOMMAND
Source: "Microsoft OLE DB Provider for Oracle"
IErrorInfo: [0x00000000] "One or more errors occurred during processing of command."
Using the same query in a Microsoft Visual Basic ADO application generates the following error:
Run-time error '-2147217900 (80040e14)':
Syntax Error in {oj...} ODBC Escape.
Here is the code for the Visual Basic application:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDAORA;Data Source=dseOracle8;User ID=demo;Password=demo"
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM {oj tab1 INNER JOIN tab2 ON tab1.col1=tab2.col2}", cnn, adOpenStatic, adLockOptimistic
APPLIES TO
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
| kbdatabase kboracle kbprb kbprovider KB245379 |
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate