Originally posted by: jvanboga

I have a query that can be executed 10s of thousands of times based on trading partner input files.  In 11g I didn't bind the variables within queries.  They are written similar to below....

  select * from table where fisrt_name = '#first#'
 

For various reasons this is no longer an efficient query in 12c.   The related map running an 80 meg input file against 11g takes 1 hour.  In 12c the same file ran over 14 hours before we killed it.  So, I tested the query below using an SQL tool and received results.

  variable fname varchar2
  exec :fname =:'bob';
  select * from table where fisrt_name = :fname

I then updated my DBID file as shown below and ran the map.

  variable fname varchar2
  exec :fname =:'#first#';
  select * from table where fisrt_name = :fname
 

The map failed with this msg...(FYI - the solution above should have worked for both 11g and 12c)...............

<4088-4340-05/31/17 13:59:32>: GET Function, Transaction scope: Map
<4088-4340-05/31/17 13:59:32>: TRACE command specified, file: e:\wtx84\apps\edi\in\edi_837\logs\MEMBER_CLAIM_SYSTEM_STATUS_sub_emp_1c.dbl
<4088-4340-05/31/17 13:59:32>: Error returned by OCIStmtExecute, errcode=900

ORA-00900: invalid SQL statement

<4088-4340-05/31/17 13:59:32>: Returned status: (-1009) Failed_to_execute_the_SQL_statement

Any thoughts/suggestions/solutions?