Not all variables bound oracle ошибка

ORA-01008

ORA-01008: не все переменные связанны

Причина:

SQL оператор содержащий подстановочные переменные выполняется без связанности всех переменных. Все подстановочные переменные должны иметь подстановочные значения перед выполнением SQL оператора.

Действие:

В OCI, используйте OBIND или OBINDN вызов для подстановки требуемых значений.

I have come across an Oracle problem for which I have so far been unable to find the cause.
The query below works in Oracle SQL developer, but when running in .NET it throws:

ORA-01008: not all variables bound

I’ve tried:

  • Changing the Oracle data type for lot_priority (Varchar2 or int32).
  • Changing the .NET data type for lot_priority (string or int).
  • One bind variable name is used twice in the query. This is not a problem in my
    other queries that use the same bound variable in more than one
    location, but just to be sure I tried making the second instance its
    own variable with a different :name and binding it separately.
  • Several different ways of binding the variables (see commented code;
    also others).
  • Moving the bindByName() call around.
  • Replacing each bound variable with a literal. I’ve had two separate variables cause the problem (:lot_pri and :lot_priprc). There were some minor changes I can’t remember between the two. Changing to literals made the query work, but they do need to work with binding.

Query and code follow. Variable names have been changed to protect the innocent:

SELECT rf.myrow floworder, rf.stage, rf.prss,
rf.pin instnum, rf.prid, r_history.rt, r_history.wt
FROM
(
    SELECT sub2.myrow, sub2.stage, sub2.prss, sub2.pin, sub2.prid
    FROM (
        SELECT sub.myrow, sub.stage, sub.prss, sub.pin,
            sub.prid, MAX(sub.target_rn) OVER (ORDER BY sub.myrow) target_row
            ,sub.hflag
        FROM (
            WITH floc AS 
            (
                SELECT flow.prss, flow.seq_num
                FROM rpf@mydblink flow
                WHERE flow.parent_p = :lapp
                AND flow.prss IN (
                    SELECT r_priprc.prss
                    FROM r_priprc@mydblink r_priprc
                    WHERE priprc = :lot_priprc
                )
                AND rownum = 1
            )
            SELECT row_number() OVER (ORDER BY pp.seq_num, rpf.seq_num) myrow,
                rpf.stage, rpf.prss, rpf.pin,
                rpf.itype, hflag,
            CASE WHEN rpf.itype = 'SpecialValue'
                THEN rpf.instruction
                ELSE rpf.parent_p
            END prid,
            CASE WHEN rpf.prss = floc.prss
                AND rpf.seq_num = floc.seq_num
                THEN row_number() OVER (ORDER BY pp.seq_num, rpf.seq_num)
            END target_rn
            FROM floc, rpf@mydblink rpf
            LEFT OUTER JOIN r_priprc@mydblink pp
                ON (pp.prss = rpf.prss)
            WHERE pp.priprc = :lot_priprc
            ORDER BY pp.seq_num, rpf.seq_num
        ) sub
    ) sub2
    WHERE sub2.myrow >= sub2.target_row
    AND sub2.hflag = 'true'
) rf
LEFT OUTER JOIN r_history@mydblink r_history
ON (r_history.lt = :lt
    AND r_history.pri = :lot_pri
    AND r_history.stage = rf.stage
    AND r_history.curp = rf.prid
)
ORDER BY myrow

public void runMyQuery(string lot_priprc, string lapp, string lt, int lot_pri) {
Dictionary<int, foo> bar = new Dictionary<int, foo>();
using(var con = new OracleConnection(connStr)) {
    con.Open();

    using(var cmd = new OracleCommand(sql.rtd_get_flow_for_lot, con)) { // Query stored in sql.resx
        try {
            cmd.BindByName = true;
            cmd.Prepare();
            cmd.Parameters.Add(new OracleParameter("lapp", OracleDbType.Varchar2)).Value = lapp;
            cmd.Parameters.Add(new OracleParameter("lot_priprc", OracleDbType.Varchar2)).Value = lot_priprc;
            cmd.Parameters.Add(new OracleParameter("lt", OracleDbType.Varchar2)).Value = lt;
            // Also tried OracleDbType.Varchar2 below, and tried passing lot_pri as an integer
            cmd.Parameters.Add(new OracleParameter("lot_pri", OracleDbType.Int32)).Value = lot_pri.ToString();
            /*********** Also tried the following, more explicit code rather than the 4 lines above: **
            OracleParameter param_lapp
                = cmd.Parameters.Add(new OracleParameter("lapp", OracleDbType.Varchar2));
            OracleParameter param_priprc
                = cmd.Parameters.Add(new OracleParameter("lot_priprc", OracleDbType.Varchar2));
            OracleParameter param_lt
                = cmd.Parameters.Add(new OracleParameter("lt", OracleDbType.Varchar2));
            OracleParameter param_lot_pri
                = cmd.Parameters.Add(new OracleParameter("lot_pri", OracleDbType.Varchar2));
            param_lapp.Value = lastProcedureStackProcedureId;
            param_priprc.Value = lotPrimaryProcedure;
            param_lt.Value = lotType;
            param_lot_pri.Value = lotPriority.ToString();
            //***************************************************************/
            var reader = cmd.ExecuteReader();
            while(reader.Read()) {
                // Get values from table (Never reached)
            }
        }
        catch(OracleException e) {
            //     ORA-01008: not all variables bound
        }
    }
}

Why is Oracle claiming that not all variables are bound?

875438Sep 13 2011 — edited Sep 13 2011

Can anyone give me a brief explanation about this error ?

ORA-01008: not all variables bound

This post has been answered by prakash on Sep 13 2011

Jump to Answer

Comments

Locked Post

New comments cannot be posted to this locked post.

Впервые я столкнулся с Oracle, и мне трудно понять, почему я получаю эту ошибку.

Я использую Oracle ODT.NET w/С# со следующим кодом в запросе where where:

WHERE table.Variable1 = :VarA
  AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%')
  AND (:VarC IS NULL OR table.Variable3 LIKE :VarC || '%')

и я добавляю значения параметров так:

cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");

Когда я запускаю этот запрос, сервер возвращает:

ORA-01008: not all variables bound 

Если я прокомментирую любую из строк «AND (….» ), запрос завершается успешно.

Почему запрос выполнялся нормально, если я запрашиваю только два параметра, но не с тремя? Ошибка, которую я получаю, даже не имеет смысла

4b9b3361

Ответ 1

Поставщик ODP.Net из oracle использует привязку по позиции по умолчанию. Чтобы изменить поведение для привязки по имени. Установите свойство BindByName в true. Чем вы можете отклонить двойное определение параметров.

using(OracleCommand cmd = con.CreateCommand()) {
    ...
    cmd.BindByName = true;
    ...
}

Ответ 2

Это кажется глупым, но я думаю, что когда вы используете одну и ту же переменную связывания дважды, вам нужно установить ее дважды:

cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");
cmd.Parameters.Add("VarC", "1234");

Конечно, это правда с Native Dynamic SQL в PL/SQL:

SQL> begin
  2     execute immediate 'select * from emp where ename=:name and ename=:name'
  3     using 'KING';
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-01008: not all variables bound


SQL> begin
  2     execute immediate 'select * from emp where ename=:name and ename=:name' 
  3     using 'KING', 'KING';
  4  end;
  5  /

PL/SQL procedure successfully completed.

Ответ 3

Вы также можете рассмотреть возможность удаления дубликатов имен параметров в вашем Sql, изменив ваш Sql на

table.Variable2 LIKE '%' || :VarB || '%'

а затем, чтобы ваш клиент предоставил «%» для любого значения VarB вместо null. В некотором смысле я считаю, что это более естественно.

Вы также можете изменить Sql на

table.Variable2 LIKE '%' || IfNull(:VarB, '%') || '%'

If you’re a developer working with Oracle databases, you may have encountered the ORA-01008 error message: «Not all variables bound.» This error occurs when a SQL query has placeholders for bind variables (parameters), but not all of them have been assigned a value. When this happens, Oracle cannot execute the query because it doesn’t know what values to substitute for the placeholders.

Fortunately, the solution is usually straightforward. In this guide, we’ll walk through the steps you can take to troubleshoot and resolve the ORA-01008 error.

Step 1: Check Your SQL Query

The first step in troubleshooting the ORA-01008 error is to review your SQL query and make sure that all of the placeholders (bind variables) have been assigned a value. For example, if your query looks like this:

SELECT * FROM employees WHERE hire_date > :date AND salary > :salary;

You need to make sure that both :date and :salary have been assigned values before executing the query.

Step 2: Verify Your Bind Variables

Next, you should verify that the values you’ve assigned to the bind variables match the data type and length specified in your query. For example, if you’ve assigned a string value to a bind variable that’s expecting a number, you’ll get the ORA-01008 error.

Step 3: Check Your Connection

If your SQL query and bind variables are correct, the next thing to check is your database connection. Make sure that you’re connected to the correct database and that your connection is still active.

Step 4: Try Rebinding Your Variables

If none of the above steps have resolved the ORA-01008 error, you can try rebinding your variables. This involves assigning values to your bind variables again, either through your application or by executing a PL/SQL block.

FAQ

What Causes the ORA-01008 Error?

The ORA-01008 error occurs when a SQL query has placeholders for bind variables (parameters), but not all of them have been assigned a value.

How Do I Fix the ORA-01008 Error?

To fix the ORA-01008 error, you should check your SQL query, verify your bind variables, check your connection, and try rebinding your variables.

Can the ORA-01008 Error Be Prevented?

Yes, the ORA-01008 error can be prevented by making sure that all of the placeholders in your SQL queries have been assigned a value before executing the query.

Other errors related to bind variables include ORA-01036 («illegal variable name/number») and ORA-01745 («invalid host/bind variable name»).

How Can I Debug SQL Queries with Bind Variables?

You can debug SQL queries with bind variables by using tools like Oracle SQL Developer or SQL*Plus, which allow you to step through your code and view the values of your variables at each step.

  • Oracle’s official documentation on ORA-01008
  • Oracle’s official documentation on bind variables
  • Stack Overflow thread on ORA-01008 error

totn Oracle Error Messages


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.

April 29, 2021

I got ” ORA-01008 : Not all variables bound” error in Oracle database.

ORA-01008 : Not all variables bound

Details of error are as follows.

ORA-01008 not all variables bound

Cause: A SQL statement containing substitution variables was executed without all
 variables bound. All substitution variables must have a substituted value before the 
SQL statement is executed.

Action: In OCI, use an OBIND or OBINDN call to substitute the required values


Not all variables bound

This ORA-01008 errors are related with the SQL statement containing substitution variables was executed without all
variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

To solve this error, In OCI, use an OBIND or OBINDN call to substitute the required values.

If you used the NULL value, you can get this error, you can use the DBNull.Value instead of NULL.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 1,496 views last month,  9 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like
first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, commission_pct, manager_id, department_id.

I am writting a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt.
If you enter 12000, it should display all employees earning more than 12000.
Eg: Salary_value: 12000.

My report is like this

select last_name, salary
from employees
where employees.salary>&sal;

But I am getting an error like this

ORA-01008: not all variables bound
01008. 00000 - "not all variables bound"
*Cause:
*Action:
Vendor code 1008

Clearly there is nothing in the cause section. I need help please. Thanks in advance.

BUT I can succesfully run it from Sql plus command line utility as well as I can run it as a *.sql file. But It is not working in case of a report. Why is this happening?

Понравилась статья? Поделить с друзьями:
  • Nr4140 ошибки мерседес
  • Nr 4143 ошибка мерседес актрос
  • Nr 155 navien ошибка 02
  • Npmproxy dll как исправить ошибку
  • Npm исправить ошибки