Microsoft KB Archive/245379

= PRB: MS OLE DB Provider for Oracle Doesn't Support INNER JOIN Syntax with ODBC Escape Sequence =

Article ID: 245379

Article Last Modified on 9/30/2003

-

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

-



This article was previously published under Q245379



SYMPTOMS
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.



CAUSE
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.



RESOLUTION
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



STATUS
This behavior is by design.



MORE INFORMATION
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

Keywords: kboracle kbprovider kbdatabase kbprb KB245379

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.