AnsweredAssumed Answered

Oracle Database Standard Update - Invalid Column Index error

Question asked by allan.ford577519 on Jun 23, 2017
Latest reply on Jun 26, 2017 by allan.ford577519

Hi ! 

 

I am trying to use a Database Standard Update with an Oracle database.

 

I am using a Standard Update rather than Dynamic update as the Dynamic update doesn't seem to want to update the target DB with a null value when the source system supplies a null. 

 

 

 

 

If I have the write SQL to file option switched on then the file contains valid SQL that works fine.

 

If I have the write SQL to file option switched off, (i.e. want to update the database) ..  I get an error : Invalid Column Index error.  "java.sql.SQLException: Invalid column index"

 

Has anyone else had this issue?

 

 

 

 

e.g. SQL written to debug file (for Oracle DB) is this .. (which works fine ..) 


UPDATE PIMSDB.BASIC_WELLS
set
LINE_NAME = nvl('',null)
,SHOTPOINT_NUMBER = nvl('',null)
,SPUD_DATE = to_date(substr('',1,15),'YYYYMMDD HH24MISS')
where
WELL_CODE = 2
;
UPDATE PIMSDB.BASIC_WELLS
set
LINE_NAME = nvl('81-QME',null)
,SHOTPOINT_NUMBER = nvl('185',null)
,SPUD_DATE = to_date(substr('19820216 000000.000',1,15),'YYYYMMDD HH24MISS')
where
WELL_CODE = 3
;
UPDATE PIMSDB.BASIC_WELLS
set
LINE_NAME = nvl('85-YJA',null)
,SHOTPOINT_NUMBER = nvl('494',null)
,SPUD_DATE = to_date(substr('19860626 000000.000',1,15),'YYYYMMDD HH24MISS')
where
WELL_CODE = 4
;
UPDATE PIMSDB.BASIC_WELLS
set
LINE_NAME = nvl('84-STH',null)
,SHOTPOINT_NUMBER = nvl('469',null)
,SPUD_DATE = to_date(substr('19850313 000000.000',1,15),'YYYYMMDD HH24MISS')
where
WELL_CODE = 5
;

 

 

 

 

 

Full Error:

 

java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setBigDecimalInternal(OraclePreparedStatement.java:5281)
at oracle.jdbc.driver.OraclePreparedStatement.setBigDecimal(OraclePreparedStatement.java:5273)
at com.boomi.connector.database.types.NumericType.setValue(NumericType.java:24)
at com.boomi.connector.database.types.ValueType.applyValue(ValueType.java:37)
at com.boomi.connector.database.statement.DatabaseParameter.applyValue(DatabaseParameter.java:70)
at com.boomi.connector.database.statement.SqlStatementUtility.applyValues(SqlStatementUtility.java:42)
at com.boomi.connector.database.statement.SqlStatement.prepare(SqlStatement.java:190)
at com.boomi.connector.database.executor.SQLWriteExecutor.execute(SQLWriteExecutor.java:61)
at com.boomi.connector.database.DatabaseSend.executeData(DatabaseSend.java:192)
at com.boomi.connector.database.DatabaseSend.executeStatement(DatabaseSend.java:144)
at com.boomi.connector.database.DatabaseSend.executeDocument(DatabaseSend.java:121)
at com.boomi.connector.database.DatabaseSend.handle(DatabaseSend.java:297)
at com.boomi.connector.database.DatabaseSend.send(DatabaseSend.java:72)
at com.boomi.connector.custom.CustomSendConnectorAction.invoke(CustomSendConnectorAction.java:60)
at com.boomi.connector.base.BaseConnectorAction.invokeBase(BaseConnectorAction.java:366)
at com.boomi.connector.base.BaseConnectorAction.invokeWithReadStore(BaseConnectorAction.java:305)
at com.boomi.connector.base.BaseConnectorAction.invoke(BaseConnectorAction.java:277)
at sun.reflect.GeneratedMethodAccessor29.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.boomi.util.ClassUtil$InvokerWithClassLoader.invoke(ClassUtil.java:369)
at com.sun.proxy.$Proxy44.invoke(Unknown Source)
at com.boomi.process.shape.ConnectorShapeHandler.execute(ConnectorShapeHandler.java:152)
at com.boomi.process.shape.ConnectorShape.execute(ConnectorShape.java:143)
at com.boomi.process.graph.ProcessShape.executeShape(ProcessShape.java:558)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:488)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.execute(ProcessGraph.java:307)
at com.boomi.process.ProcessExecution.call(ProcessExecution.java:811)
at com.boomi.execution.ExecutionTask.call(ExecutionTask.java:935)
at com.boomi.execution.ExecutionTask.call(ExecutionTask.java:61)
at com.boomi.util.concurrent.CancellableFutureTask.run(CancellableFutureTask.java:160)
at com.boomi.execution.NestedExecutionForker$NestedSynchronousExecutionFuture.runSynchronous(NestedExecutionForker.java:203)
at com.boomi.execution.NestedExecutionForker$NestedSynchronousExecutionFuture.get(NestedExecutionForker.java:171)
at com.boomi.execution.NestedExecutionForker$NestedSynchronousExecutionFuture.get(NestedExecutionForker.java:150)
at com.boomi.execution.ExecutionTask.getChildResult(ExecutionTask.java:1307)
at com.boomi.execution.ExecutionTask.getChildResult(ExecutionTask.java:1293)
at com.boomi.process.shape.ChildInvokerShape.getChildResult(ChildInvokerShape.java:41)
at com.boomi.process.shape.CatchErrorsShape.executeInitial(CatchErrorsShape.java:250)
at com.boomi.process.shape.BaseFiberShape.execute(BaseFiberShape.java:77)
at com.boomi.process.shape.CatchErrorsShape.execute(CatchErrorsShape.java:123)
at com.boomi.process.graph.ProcessShape.executeShape(ProcessShape.java:558)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:488)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.execute(ProcessGraph.java:307)
at com.boomi.process.ProcessExecution.call(ProcessExecution.java:811)
at com.boomi.execution.ExecutionTask.call(ExecutionTask.java:935)
at com.boomi.execution.ExecutionTask.call(ExecutionTask.java:61)
at com.boomi.util.concurrent.CancellableFutureTask.run(CancellableFutureTask.java:160)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)

Outcomes