Forum Stats

  • 3,768,926 Users
  • 2,252,875 Discussions
  • 7,874,803 Comments

Discussions

OraOLEDB not working properly

Rubio
Rubio Member Posts: 2
edited Mar 12, 2018 2:32PM in Oracle Provider for OLE DB

I'm using SSIS to run a few inserts to Oracle 12.1. If I do the following, everything works fine.

<span class="kwd">MERGE</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> table1 t1 </span><span class="kwd">USING<br/></span><span class="pun">(<br/></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> table2 </span><span class="kwd">where</span><span class="pln"> </span><span class="pun">...<br/></span><span class="pun">)</span><span class="pln"> s<br/></span><span class="kwd">ON</span><span class="pln"> t1</span><span class="pun">.</span><span class="pln">Id </span><span class="pun">=</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">Id<br/> </span><span class="kwd">WHEN</span><span class="pln"> </span><span class="kwd">NOT</span><span class="pln"> </span><span class="kwd">MATCHED</span><span class="pln"> </span><span class="kwd">THEN</span><span class="pln"> </span><span class="kwd">INSERT</span><span class="pln"> </span><span class="pun">(...)</span><span class="pln"> </span><span class="kwd">VALUES</span><span class="pln"> </span><span class="pun">(...)</span>

However, if try either of the following, I get an error.

<span class="kwd">MERGE</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> table1 t1 </span><span class="kwd">USING<br/></span><span class="pun">(<br/></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from<br/></span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> table3 </span><span class="kwd">where</span><span class="pln"> </span><span class="pun">...</span><span class="pln"> </span><span class="pun">)<br/></span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> </span><span class="pun">...<br/></span><span class="pun">)</span><span class="pln"> s<br/></span><span class="kwd">ON</span><span class="pln"> t1</span><span class="pun">.</span><span class="pln">Id </span><span class="pun">=</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">Id<br/> </span><span class="kwd">WHEN</span><span class="pln"> </span><span class="kwd">NOT</span><span class="pln"> </span><span class="kwd">MATCHED</span><span class="pln"> </span><span class="kwd">THEN</span><span class="pln"> </span><span class="kwd">INSERT</span><span class="pln"> </span><span class="pun">(...)</span><span class="pln"> </span><span class="kwd">VALUES</span><span class="pln"> </span><span class="pun">(...)</span>

Or,

<span class="kwd">MERGE</span><span class="pln"> </span><span class="kwd">INTO</span><span class="pln"> table1 t1 </span><span class="kwd">USING<br/></span><span class="pun">(<br/></span><span class="pln"> </span><span class="kwd">with</span><span class="pln"> rawresults </span><span class="kwd">as<br/></span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> table3 </span><span class="kwd">where</span><span class="pln"> </span><span class="pun">...</span><span class="pln"> </span><span class="pun">)<br/></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> rawresults</span><span class="pun">,</span><span class="pln"> table2 t2 </span><span class="kwd">where</span><span class="pln"> </span><span class="pun">...<br/></span><span class="pun">)</span><span class="pln"> s<br/></span><span class="kwd">ON</span><span class="pln"> t1</span><span class="pun">.</span><span class="pln">Id </span><span class="pun">=</span><span class="pln"> s</span><span class="pun">.</span><span class="pln">Id<br/> </span><span class="kwd">WHEN</span><span class="pln"> </span><span class="kwd">NOT</span><span class="pln"> </span><span class="kwd">MATCHED</span><span class="pln"> </span><span class="kwd">THEN</span><span class="pln"> </span><span class="kwd">INSERT</span><span class="pln"> </span><span class="pun">(...)</span><span class="pln"> </span><span class="kwd">VALUES</span><span class="pln"> </span><span class="pun">(...)</span>

Both of the latter examples work just fine when executed in SQL Developer. If I execute these in SSIS using Oracle provider for OLE DB, I get ORA-00903 invalid table name. Also, in the case of the middle example, if a create a view for the inline subquery and use that in the MERGE INTO statement, it works. Unfortunately, SSIS error reporting doesn't show which table name or line number is causing the error.

Is this a known restriction in Oracle provider for OLE DB? If so, is it documented somewhere?

I'm using OraOLEDB 12.1.0.1.0.

This discussion has been closed.