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
User364663285 posted
Hi,
Here are the programs.
my_cv.aspx
<%@ Page Language=»C#» AutoEventWireup=»true» CodeBehind=»my_cv.aspx.cs» Inherits=»NewSite.my_cv» %>
<!DOCTYPE html PUBLIC «-//W3C//DTD XHTML 1.0 Transitional//EN» «http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd«>
<html xmlns=»http://www.w3.org/1999/xhtml» >
<head id=»Head1″ runat=»server»>
<title>My File</title>
</head>
<body>
<form id=»form1″ runat=»server»>
<br /><br />
<asp:GridView ID=»gv1″ runat=»server» AutoGenerateColumns=»false» Font-Size=»Small» AllowSorting=»True» DataSourceID=»ds_job_det» >
<Columns>
<asp:CommandField ShowInsertButton=»true» ShowEditButton=»true»/>
<asp:BoundField DataField=»title» HeaderText=»Title» HtmlEncode=»False» >
<HeaderStyle HorizontalAlign=»Left» width=»65px» />
<ItemStyle HorizontalAlign=»Left» width=»65px» />
</asp:BoundField>
<asp:BoundField DataField=»start_dt» HeaderText=»StartDate» HtmlEncode=»False» >
<HeaderStyle HorizontalAlign=»Left» width=»65px» />
<ItemStyle HorizontalAlign=»Left» width=»65px» />
</asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource
ID=»ds_job_det» Runat=»server»
SelectCommand=»select job_title title,to_char(start_dt,’dd-mm-rr’) start_dt from user_past_jobs where user_id=:user_id»
UpdateCommand=»update user_past_jobs set job_title=:title,start_dt=to_date(:start_dt,’dd-mm-rr’) where user_id=:user_id»
ConnectionString=»<%$ ConnectionStrings:OracleConnectionString %>»
ProviderName=»System.Data.OracleClient» >
<SelectParameters>
<asp:SessionParameter Name=»user_id» SessionField=»sv_userid2″ Type=»Int32″ Direction=»Input» DefaultValue=»0″/>
</SelectParameters>
</asp:SqlDataSource>
<div>
</div>
</form>
</body>
</html>
my_cv.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OracleClient;
namespace NewSite
{
public partial class my_cv : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ds_job_det_RowUpdating(object sender, EventArgs e)
{
LinkButton lnk = (LinkButton)sender;
GridViewRow curr_row = (GridViewRow)lnk.Parent.Parent;
ds_job_det.InsertParameters.Clear();
ds_job_det.InsertParameters.Add(«title», curr_row.Cells[0].Text);
ds_job_det.InsertParameters.Add(«start_dt», curr_row.Cells[1].Text);
ds_job_det.Insert();
}
}
}
You can have a table like this
create table user_past_jobs
(user_id number(15),
job_title varchar2(30),
start_dt date);
I really have a difficulty in solving this unbound error. If possible, could anyone pls see this?
Server Error in ‘/’ Application.
ORA-01008: not all variables bound
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OracleClient.OracleException: ORA-01008: not all variables bound
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using
the exception stack trace below.
Stack Trace:
[OracleException (0x80131938): ORA-01008: not all variables bound
]
System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +304553
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +990
System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) +431
System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +115
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053