This is the first time I’ve dealt with Oracle, and I’m having a hard time understanding why I’m receiving this error.
I’m using Oracle’s ODT.NET w/ C# with the following code in a query’s where clause:
WHERE table.Variable1 = :VarA
AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%')
AND (:VarC IS NULL OR table.Variable3 LIKE :VarC || '%')
and I’m adding the parameter values like so:
cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");
When I run this query, the server returns:
ORA-01008: not all variables bound
If I comment out either of the ‘AND (….’ lines, the query completes successfully.
Why would the query run through alright if I’m only querying with two parameters, but not with three? The error I’m receiving doesn’t even make sense
This is the first time I’ve dealt with Oracle, and I’m having a hard time understanding why I’m receiving this error.
I’m using Oracle’s ODT.NET w/ C# with the following code in a query’s where clause:
WHERE table.Variable1 = :VarA
AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%')
AND (:VarC IS NULL OR table.Variable3 LIKE :VarC || '%')
and I’m adding the parameter values like so:
cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");
When I run this query, the server returns:
ORA-01008: not all variables bound
If I comment out either of the ‘AND (….’ lines, the query completes successfully.
Why would the query run through alright if I’m only querying with two parameters, but not with three? The error I’m receiving doesn’t even make sense
ORA-01008
ORA-01008: не все переменные связанны
Причина:
SQL оператор содержащий подстановочные переменные выполняется без связанности всех переменных. Все подстановочные переменные должны иметь подстановочные значения перед выполнением SQL оператора.
Действие:
В OCI, используйте OBIND или OBINDN вызов для подстановки требуемых значений.
Learn the cause and how to resolve the ORA-01008 error message in Oracle.
Description
When you encounter an ORA-01008 error, the following error message will appear:
- ORA-01008: not all variables bound
Cause
You tried to execute a SQL statement that contained substitution variables where all variables were not bound.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
In OCI, try using an OBIND or OBINDN call to substitute the values.
Troubleshooting
Problem
Error executing LWJDBC query: ORA-01008: not all variables bound
Symptom
Error executing LWJDBC query
Advance Status error: 1008: ORA-01008: not all variables bound
[2006-03-16 09:06:12.216] ERROR [LightweightJDBCAdapter] Error executing sql query «INSERT INTO GIS41.EMPTEST (EMPNO, ENAME, HIREDATE, JOB) values (?, ?, sysdate, ?)».
[2006-03-16 09:06:12.216] ERROR SQL Error Code: 1008
[2006-03-16 09:06:12.216] ERROR SQL State: 72000
java.sql.SQLException: ORA-01008: not all variables bound
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2152)<BR >at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2035)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:287 6)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement .java:609)
at com.sterlingcommerce.woodstock.services.jdbc.lightweight.LightweightJdbcAdapterI mpl.processData(LightweightJdbcAdapterImpl.java:442)
at com.sterlingcommerce.woodstock.workflow.activity.engine.ActivityEngineHelper.inv okeService(ActivityEngineHelper.java:2072)
at com.sterlingcommerce.woodstock.workflow.activity.engine.ActivityEngineHelper.nex tMainLogic(ActivityEngineHelper.java:630)
at com.sterlingcommerce.woodstock.workflow.activity.engine.ActivityEngineHelper.nex t(ActivityEngineHelper.java:324)
at com.sterlingcommerce.woodstock.workflow.queue.WorkFlowQueueListener.doWork(WorkF lowQueueListener.java:586)
at com.sterlingcommerce.woodstock.workflow.queue.WorkFlowQueueListener.run(WorkFlow QueueListener.java:495)
at com.sterlingcommerce.woodstock.workflow.queue.WorkFlowQueueListener.onMessage(Wo rkFlowQueueListener.java:462)
at com.sterlingcommerce.woodstock.workflow.queue.WorkFlowQueueListener.onMessage(Wo rkFlowQueueListener.java:432)
at com.sterlingcommerce.woodstock.workflow.queue.wfTransporter.run(wfTransporter.ja va:316)
at com.sterlingcommerce.woodstock.workflow.queue.BasicI will Executor$Worker.run(BasicExec utor.java:493)
at java.lang.Thread.run(Thread.java:534)
The bpml for the LWJDBC adapter does not have the param# and paramtype# sequentially numbered. Example:
If the SQL statement is: INSERT INTO GIS41.EMPTEST (EMPNO, ENAME, HIREDATE, JOB) values (?, ?, sysdate, ?)
<assign to=»param1″ from=»‘1′»/>
<assign to=»paramtype1″>String</assign>
<assign to=»param2″ from=»/ProcessData/ENAME/text()»/>
<assign to=»paramtype2″>String</assign>
<assign to=»param4″ from=»/ProcessData/JOB/text()»/>
<assign to=»paramtype4″>String</assign>
Cause
The LWJDBC adapter is looking for a fourth placeholder (?) for the param4 and paramtype4 parameters.
Resolving The Problem
Make sure that the bpml sequentially numbers the param and paramtype parameters to match the number of placeholders (?) in the SQL statement.
For the SQL statement above, the bpml should be as follows:
<assign to=»param1″ from=»‘1′»/>
<assign to=»paramtype1″>String</assign>
<assign to=»param2″ from=»/ProcessData/ENAME/text()»/>
<assign to=»paramtype2″>String</assign>
<assign to=»param3″ from=»/ProcessData/JOB/text()»/>
<assign to=»paramtype3″>String</assign>
[{«Product»:{«code»:»SS3JSW»,»label»:»IBM Sterling B2B Integrator»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF002″,»label»:»AIX»},{«code»:»PF010″,»label»:»HP-UX»},{«code»:»PF012″,»label»:»IBM i»},{«code»:»PF016″,»label»:»Linux»},{«code»:»PF027″,»label»:»Solaris»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»5.2.5;5.2.4;5.2.3;5.2.2;5.2.1;5.2;5.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}}]
Historical Number
PRI29224;SCI88974