This a known Oracle bug (at least for the support in Germany. Bug number is
419989):
- Begin quote ------------------
> Bug Eintrag 419989
>
> NOT ALL VARIABLES BOUND
>
>
> Allgemeine Angaben:
>
> Eingetragen am ...........: 07.11.1996
> Plattform ................: 912
> Produkt ..................: RDBMS
> Fehlerhafte Version ......: 2.3.2
> Behoben in Version .......:
> Letzter Update am ........: 04.02.1998
>
>
> Fehlerbeschreibung:
>
>
> Define bind variables in a pl/sql script.
> Using the variables in very simple sql script will produce ORA-1008 error.
> Getting the error depends on the order of the where clauses and/or the
> items in the select clause. The following examples show the problem:
> First the pl/sql script:
> set echo on
> var var_1 varchar2(30)
> var var_2 varchar2(30)
> begin
> :var_1 := 'VALUE_1';
> :var_2 := 'VALUE_2';
> end;
> /
> pl/sql procedure successfully completed.
> Now the sql scripts, The first 1 will be one that is successful:
> select :var2 from dual
> WHERE 'VALUE_1' = :var_1
> AND 'VALUE_2' = :var_2;
> :VAR_2
> ----------------
> VALUE_2
> #
> # Now a script that fails:
> #
> select :var_2 from dual
> where 'VALUE_1' = :var_1
> and 'VALUE_2' = :var_2;
> ERROR:
> ORA-01008: not all variables bound
> #
> # Next two cases is select :var_1 and doing the same:
> #
> select :var_1 from dual
> where 'VALUE_2' = :var_2
> and 'VALUE_1' = :var_1;
> :VAR_1
> ----------------
> VALUE_1
> #
> # Sql script that fails
> #
> select :var_1 from dual
> where 'VALUE_1' = :var_1
> and 'VALUE_2' = :var_2;
> ERROR:
> ORA-01008: not all variables bound
> #
> #
> If both variable are included in the select statement then the order of
> the conditions in the where clause does not matter.
> #
> select :var_1,:var_2 from dual
> where 'VALUE_1' = :var_1
> and 'VALUE_2' = :var_2;
> :VAR_1 :VAR_2
> ----------------------------------------------------
> VALUE_1 VALUE_2
> #
> #
> select :var_1,:var_2 from dual
> where 'VALUE_2' = :var_2
> and 'VALUE_1' = :var_1;
> :VAR_1 :VAR_2
> -------------------------------------------------------
> VALUE_1 VALUE_2
>
> My customer has a third party applicatio which generates the scripts and he has
> no influence on the order in the where statements for his UNION commands so:
> variable a varchar2(20);
> variable b varchar2(20);
> begin
> :a := 'bla';
> :b := 'table';
> end;
> /
> select object_name from user_objects
> where object_type= :a
> union all
> select object_name from user_objects
> where object_name= :a
> and object_type= :b
> /
> ----> returns an ORA-1008
> Changing the order in the second where clause will solve the problem.
>
> Can somebody take a look at this. If you use two UNION's the workaround is no
> longer valid...
>
> One of our cust has the same problem and he need a fix on this asap.
> This error still exist in 7.3.3 but it cannot be reproduced with 8.0.3.
>
> Script in the usual place. Stack trace: main->sou2o->opidrv->opiodr->opiino->
> opitsk->ttcpip->opiodr->opial7->opiall0.
>
> This does not reproduce in 8.0.5.
>
> Please verify that all the variables are being correctly bound at the interface
> level, that is since duplicates are involved the correct positions or name is
> being used.
>
- End quote --------------------
Martin Haltmayer
sminni_at_giascl01.vsnl.net.in wrote:
>
> Hi,
>
> using Oracle 7.1.3.3.3 SQL*net 2.3.2.x.x & Sql*worksheet
>
> I keep getting the following errors
>
> ORA-01008 Inconsistent Datatype
> ORA-00932 Not all variables bound
> when I define Bind (substitution ) variables
>
> The errors and situation is unpredictable. In fact merely
> altering the order of columns seems to remove the problem !
>
> I reproduce a section of the code :
> error :
> SQLWKS> select ( :arg_start_date - sysdate ) d1,
> 2> ( :arg_start_date - sysdate ) d2,
> 3> ( :arg_stop_date - sysdate ) d3
> 4> from dual
> 5>
> ORA-00932: inconsistent datatypes
> but this works !:
> SQLWKS> select ( :arg_start_date - sysdate ) d1,
> 2> ( :arg_stop_date - sysdate ) d2,
> 3> ( :arg_start_date - sysdate ) d3
> 4> from dual
> 5>
> D1 D2 D3
> ---------- ---------- ----------
>
> 1 row selected.
> Note : the only difference is that position of column d2 & d3 is interchanged
>
> The error message varies between 00932 & 01008
>
> Any help will be appreciated as some of our vital reports are help up.
> Pl mark a copy directly to me - sminni_at_giascl01.vsnl.net.in
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sat Feb 14 1998 - 00:00:00 CST
