6 Replies Latest reply: Feb 21, 2013 8:43 AM by BluShadow RSS

    ORA-01008: not all variables bound

    946279
      is it possible to avoid multiple values specification for one bind variable, ie. to specify only one 'X' after using clause in the example below? if so, how should it be written properly?
      declare
        s varchar2(100);
      begin
        s := ' select rowid from dual where dummy = :1 ' ||
             ' union all ' ||
             ' select rowid from dual where dummy = :1 ';
        execute immediate s using 'X', 'X';
      end;
      thank you
        • 1. Re: ORA-01008: not all variables bound
          Karthick_Arp
          943276 wrote:
          is it possible to avoid multiple values specification for one bind variable, ie. to specify only one 'X' after using clause in the example below? if so, how should it be written properly?
          declare
          s varchar2(100);
          begin
          s := ' select rowid from dual where dummy = :1 ' ||
          ' union all ' ||
          ' select rowid from dual where dummy = :1 ';
          execute immediate s using 'X', 'X';
          end;
          thank you
          Binding is by position. The name specified for the variable is not relevent. So if you have 10 bind variables, you need to specify 10 values.
          • 2. Re: ORA-01008: not all variables bound
            946279
            thanks!
            • 3. Re: ORA-01008: not all variables bound
              701278
              You can use DBMS_SQL and bind by variable name:
              SQL> SET SERVEROUTPUT ON
              SQL> DECLARE
                2     l_cursor INTEGER := dbms_sql.open_cursor;
                3     s        VARCHAR2(200);
                4     l_rowid UROWID;
                5     n INTEGER;
                6  BEGIN
                7     s := ' select rowid from dual where dummy = :1
                8            union all
                9            select rowid from dual where dummy = :1';
               10     dbms_sql.parse(l_cursor, s, dbms_sql.native);
               11     dbms_sql.define_column(l_cursor, 1, l_rowid);
               12     -- bind once for each distinct variable name
               13     dbms_sql.bind_variable(l_cursor, ':1', 'X');
               14     -- execute
               15     n := dbms_sql.execute_and_fetch(l_cursor);
               16     dbms_sql.column_value(l_cursor, 1, l_rowid);
               17     dbms_output.put_line(l_rowid);
               18  END;
               19  /
               
              AAAADDAABAAAAHSAAA
               
              PL/SQL procedure successfully completed
              • 4. Re: ORA-01008: not all variables bound
                padders
                is it possible to avoid multiple values specification for one bind variable
                Binding with native dynamic SQL (EXECUTE IMMEDIATE or OPEN FOR) is by position.

                You could consider rewriting the SQL itself so there is only one bind variable.

                Alternatively you could consider using the DBMS_SQL package to parse the SQL which allows you to bind variables by name.
                • 5. Re: ORA-01008: not all variables bound
                  BluShadow
                  That's a benefit of subquery factoring (the WITH clause)...
                  declare
                    s varchar2(100);
                  begin
                    s := ' with t as (select :1 as vl from dual) '||
                         ' select rowid from dual, t where dummy = t.vl ' ||
                         ' union all ' ||
                         ' select rowid from dual, t where dummy = t.vl ';
                    execute immediate s using 'X';
                  end;
                  Edit: in your particular case you have an ambiguous column name (rowid) as it naturally exists in all tables/views queried...
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  declare
                    2    s varchar2(1000);
                    3  begin
                    4    s := ' with t as (select :1 as vl from dual) '||
                    5         ' select dual.rowid from dual, t where dummy = t.vl ' ||
                    6         ' union all ' ||
                    7         ' select dual.rowid from dual, t where dummy = t.vl ';
                    8    execute immediate s using 'X';
                    9* end;
                  SQL> /
                  
                  PL/SQL procedure successfully completed.
                  Edited by: BluShadow on 21-Feb-2013 14:42
                  • 6. Re: ORA-01008: not all variables bound
                    Frank Kulash
                    Hi,
                    943276 wrote:
                    is it possible to avoid multiple values specification for one bind variable, ie. to specify only one 'X' after using clause in the example below? if so, how should it be written properly?
                    declare
                    s varchar2(100);
                    begin
                    s := ' select rowid from dual where dummy = :1 ' ||
                    ' union all ' ||
                    ' select rowid from dual where dummy = :1 ';
                    execute immediate s using 'X', 'X';
                    end;
                    thank you
                    If you want to use the same value 2 (or more) times in the query, you can always use a parameter "table" (such as param, below), and then reference the value (e.g., param.d) as often as you need to:
                    declare
                      s varchar2(1000);
                    begin
                      s := 'WITH  param  AS '     ||
                           '( '                         ||
                           '     SELECT  :1 AS d '     ||
                           '     FROM    dual '       ||
                           ') '                        || 
                           'select  dual.rowid from dual cross join param where dual.dummy = param.d ' ||
                           ' union all ' ||
                           'select  dual.rowid from dual cross join param where dual.dummy = param.d ';
                      execute immediate s using 'X';
                    end;
                    /
                    This may be less efficient than just repeating the value in the USING clause, as well has harder to code.

                    I can understand why you would want to avoid the maintenance headache of having to change 2 bind variables that are always supposed to be the same. Here is a better way to do that while still passing 2 values to the dynamic SQL statement:
                    declare
                      s varchar2(100);
                      d dual.dummy%TYPE     := 'X';          -- Set 1 time here
                    begin
                      s := ' select rowid from dual where dummy = :1 ' ||
                           ' union all ' ||
                           ' select rowid from dual where dummy = :1 ';
                      execute immediate s using d, d;          -- Use N times here
                    end;
                    /
                    Edited by: Frank Kulash on Feb 21, 2013 9:47 AM