Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01008: not all variables bound ORA-06512
ORA-01008: not all variables bound ORA-06512 [message #187088] Thu, 10 August 2006 19:40 Go to next message
archie1
Messages: 3
Registered: August 2006
Junior Member
I am modifying a oracle function. if i add line AND o.operation_id = :OPERATION_ID as mentioned in orange i get ORA-01008: not all variables bound ORA-06512. I am new in Oracle can you please suggest me what is wrong

LotBinSummQryStr := 'WITH ' ||
'lots AS ' ||
'( SELECT l.lot_id, ' ||
'l.lot_lnkid, ' ||
'l.excluded , o.operation_lnkid ' ||
' FROM amp_lot l,amp_lot_op lo,amp_operation o ' ||
' WHERE l.lot_id = :LOT_ID and lo.operation_lnkid =o.operation_lnkid ' ||
' AND o.operation_id = :OPERATION_ID and lo.lot_lnkid =l.lot_lnkid'||
'), ' ||
'total AS ' ||
'( SELECT SUM(woy.total_die_qty) AS total ' ||
'FROM amp_wafer_op_yield woy, ' ||
'amp_design_rev_op do, '||
'amp_operation o, ' ||
'lots ' ||
'WHERE woy.lot_lnkid = lots.lot_lnkid ' ||
'AND woy.operation_lnkid = o.operation_lnkid ' ||
'AND woy.design_rev_op_lnkid = do.design_rev_op_lnkid ' ||
'AND woy.design_revision_lnkid = :DEVICE_HANDLE ' ||
ApplyStatLimitsQryStr || ApplyShipLimitsQryStr ||
' AND woy.source = :ELECTRICAL_SOURCE ' ||
'AND o.operation_id = :OPERATION_ID ' ||
') ' ||
'SELECT bin_order, ' ||
'CASE ' ||
'WHEN label = ''Bin: Other'' ' ||
'THEN ''Other'' ' ||
'ELSE label ' ||
'END AS bin_number, ' ||
'CASE ' ||
' WHEN label = ''Bin: Other'' ' ||
' THEN ''Other'' ' ||
' ELSE MAX(bin_name) ' ||
' END AS bin_name, ' ||
'SUM(bin_qty) AS quantity, ' ||
'SUM(bin_percentage) AS percent ' ||
'FROM (SELECT CASE ' ||
'WHEN (ROWNUM <= 10) ' ||
' THEN ROWNUM ' ||
' ELSE 11 ' ||
' END AS bin_order, ' ||
' CASE ' ||
' WHEN (ROWNUM <= 10) ' ||
' THEN '''' || bin_number ' ||
' ELSE ''Bin: Other'' ' ||
' END AS label, ' ||
' bin_percentage AS bin_percentage, ' ||
' bin_name AS bin_name, ' ||
' bin_qty AS bin_qty ' ||
' FROM (SELECT b.bin_number AS bin_number, ' ||
' b.bin_name AS bin_name, ' ||
' SUM(wobs.bin_qty) AS bin_qty, ' ||
' 100 ' ||
' * SUM(wobs.bin_qty) ' ||
' / total.total AS bin_percentage ' ||
' FROM amp_wafer_op_bin_summary wobs, ' ||
' amp_wafer_op_yield woy, ' ||
' amp_design_rev_op do, ' ||
' amp_operation o, ' ||
' amp_bin b, ' ||
' lots, ' ||
' total , ' ||
' wafer wf' ||
' WHERE wobs.lot_lnkid = lots.lot_lnkid ' ||
' AND woy.lot_wafer_op_lnkid = wobs.lot_wafer_op_lnkid ' ||
' AND woy.design_rev_op_lnkid = do.design_rev_op_lnkid ' ||
' AND woy.source = wobs.source ' ||
' AND wobs.operation_lnkid = o.operation_lnkid ' ||
' AND wobs.bin_lnkid = b.bin_lnkid ' ||
' AND wobs.design_revision_lnkid = :DEVICE_HANDLE ' ||
' AND wobs.lot_wafer_lnkid = wf.wafer_lnkid' ||
ApplyStatLimitsQryStr || ApplyShipLimitsQryStr || ExcludedWafersQryStr ||
' AND wobs.source = :ELECTRICAL_SOURCE ' ||
' AND o.operation_id = :OPERATION_ID ' ||
FailBinOnlyQryStr ||
' GROUP BY total.total, ' ||
' wobs.bin_lnkid, ' ||
' b.bin_number, ' ||
' b.bin_name ' ||
' ORDER BY bin_percentage DESC)) ' ||
'GROUP BY bin_order, label ' ||
'ORDER BY bin_order';
Re: ORA-01008: not all variables bound ORA-06512 [message #187107 is a reply to message #187088] Thu, 10 August 2006 21:57 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Has the value for :OPERATION_ID been substituted before the SQL is executed?
Re: ORA-01008: not all variables bound ORA-06512 [message #187111 is a reply to message #187107] Thu, 10 August 2006 22:11 Go to previous messageGo to next message
archie1
Messages: 3
Registered: August 2006
Junior Member
yes. Also AND variable :OPERATION_ID is been used at other part in this query and it works just fine but if i add this codition to the particular place (i have high lighted in orange) then i get variable bound error
Re: ORA-01008: not all variables bound ORA-06512 [message #187118 is a reply to message #187088] Thu, 10 August 2006 23:11 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
How do you execute the query? If using EXECUTE IMMEDIATE then with the extra bind variable, you need to match it with a corresponding value for its new position even if the same variable was used before.
SQL> DECLARE
  2    qry        VARCHAR2(1000);
  3    vout       VARCHAR2(100);
  4  BEGIN
  5    qry :=  'WITH ' ||
  6            'data AS ' ||
  7            '(SELECT column_1, column_2 FROM tnum ' ||
  8            'WHERE column_3 = :C3) ' ||
  9            'SELECT column_1 FROM data WHERE column_2 = :C2';
 10    EXECUTE IMMEDIATE qry INTO vout USING '200', 'ghi';
 11    dbms_output.put_line('Output: '||vout);
 12  END;
 13  /
Output: 4

PL/SQL procedure successfully completed.

SQL> 
SQL> DECLARE
  2    qry        VARCHAR2(1000);
  3    vout       VARCHAR2(100);
  4  BEGIN
  5    qry :=  'WITH ' ||
  6            'data AS ' ||
  7            '(SELECT column_1, column_2 FROM tnum ' ||
  8            'WHERE column_3 = :C3 AND column_2 = :C2) ' ||
  9            'SELECT column_1 FROM data WHERE column_2 = :C2';
 10  
 11    EXECUTE IMMEDIATE qry INTO vout USING '200', 'ghi';
 12    dbms_output.put_line('Output: '||vout);
 13  END;
 14  /
DECLARE
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 11


SQL> 
SQL> DECLARE
  2    qry        VARCHAR2(1000);
  3    vout       VARCHAR2(100);
  4  BEGIN
  5    qry :=  'WITH ' ||
  6            'data AS ' ||
  7            '(SELECT column_1, column_2 FROM tnum ' ||
  8            'WHERE column_3 = :C3 AND column_2 = :C2) ' ||
  9            'SELECT column_1 FROM data WHERE column_2 = :C2';
 10  
 11    EXECUTE IMMEDIATE qry INTO vout USING '200', 'ghi', 'ghi';
 12    dbms_output.put_line('Output: '||vout);
 13  END;
 14  /
Output: 4

PL/SQL procedure successfully completed.

If this doesn't help, post the code where you substitute variable values.
Re: ORA-01008: not all variables bound ORA-06512 [message #187121 is a reply to message #187118] Fri, 11 August 2006 00:08 Go to previous messageGo to next message
archie1
Messages: 3
Registered: August 2006
Junior Member
Thanks Hobbes!!!
bug is fixed with your help. I didnt know that with the extra bind variable, we need to match it with a corresponding value for its new position even if the same variable was used before. I had matched extra operation_id variable and it worked. Also as you asked that how i am exucuting this query. It is a oracle function which is called by another procedure so debugging was a little tough.

Thanks alot again. I appreciate it.

Archie
Re: ORA-01008: not all variables bound ORA-06512 [message #597765 is a reply to message #187121] Tue, 08 October 2013 06:56 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

That is very good information .But I have one doubt over here that what is the mapping sequnce to be maintained while using bind variable.

 'WHERE column_3 = :C3 AND column_2 = :C2) ' ||
  9            'SELECT column_1 FROM data WHERE column_2 = :C2';

EXECUTE IMMEDIATE qry INTO vout USING '200', 'ghi', 'ghi';

The sequnce of mapping the bind variable is like top to bottom and left to right apporach. That means
whatever encountered (C3,C2,C2) would be mapped to values respectively .
icon5.gif  Re: ORA-01008: not all variables bound ORA-06512 [message #597767 is a reply to message #597765] Tue, 08 October 2013 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And why don't you try and post us the answer?

Re: ORA-01008: not all variables bound ORA-06512 [message #597772 is a reply to message #597767] Tue, 08 October 2013 07:17 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

No Michel,

I was trying with out giving the extra bind variable and failed with error then I searched and got the solution from this thread,So there is no problem left .Just for asking though the bind varible name is same why cant oracle use the same when we give it. As follows

DECLARE
  p_empno  INTEGER;
  p_deptno INTEGER;
  str      VARCHAR2(400);
  l_ename  VARCHAR2(30);
BEGIN
  p_empno :=7839;
  p_deptno:=10;
  str     :='select ename from emp 
              where empno=:p_empno and deptno=:p_deptno 
              and exists (select null from dept where dept.deptno=emp.deptno and dept.deptno=:p_deptno)';
  EXECUTE immediate str INTO l_ename USING p_empno,p_deptno;
end;

ORA-01008: not all variables bound
ORA-06512: at line 10
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:

[Updated on: Tue, 08 October 2013 07:18]

Report message to a moderator

Re: ORA-01008: not all variables bound ORA-06512 [message #597778 is a reply to message #597772] Tue, 08 October 2013 07:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3122
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Tue, 08 October 2013 17:47
Just for asking though the bind varible name is same why cant oracle use the same when we give it.

  str     :='select ename from emp 
              where empno=:p_empno and deptno=:p_deptno 
              and exists (select null from dept where dept.deptno=emp.deptno and dept.deptno=:p_deptno)';
  EXECUTE immediate str INTO l_ename USING p_empno,p_deptno;



How would Oracle assume which bind variable to bind as per your question. That is the way it works. You have to specify -

EXECUTE immediate str INTO l_ename USING p_empno,p_deptno,p_deptno;


Re: ORA-01008: not all variables bound ORA-06512 [message #597779 is a reply to message #597778] Tue, 08 October 2013 07:35 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

But I already have assigned specific value to the particular bind varibale .
like p_empno :=7839; p_deptno:=10;

It could use that but not doing may because of it is not regular varibale and that is why it is called as bind varibale. Smile



Re: ORA-01008: not all variables bound ORA-06512 [message #597780 is a reply to message #597778] Tue, 08 October 2013 07:44 Go to previous messageGo to next message
DrabJay
Messages: 32
Registered: May 2013
Member
You should note that "if you repeat placeholder names in dynamic SQL statements, be aware that the way placeholders are associated with bind variables depends on the kind of dynamic SQL statement" as per the documentation.
Re: ORA-01008: not all variables bound ORA-06512 [message #597791 is a reply to message #597780] Tue, 08 October 2013 08:24 Go to previous messageGo to next message
sss111ind
Messages: 563
Registered: April 2012
Location: India
Senior Member

DrabJay, you made a good point here really .


in sql
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
   execute immediate sql_stmt using a, a, b, a;
   
in Pl/sql   
CREATE PROCEDURE calc_stats (
  w NUMBER,
  x NUMBER,
  y NUMBER,
  z NUMBER )
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
BEGIN
  plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b;  -- calc_stats(a, a, b, a)
end;


so for the select statment also we should include as much bind varibles as the above insert statment takes.

DECLARE
  p_empno  INTEGER;
  p_deptno INTEGER;
  str      VARCHAR2(400);
  l_ename  VARCHAR2(30);
BEGIN
  p_empno :=7839;
  p_deptno:=10;
  str     :='select ename from emp 
              where empno=:p_empno and deptno=:p_deptno 
              and exists (select null from dept where dept.deptno=emp.deptno and dept.deptno=:p_deptno)';
  execute immediate str into l_ename using p_empno,p_deptno,p_deptno;-- a,b,b
  dbms_output.put_line(str);
end;
Re: ORA-01008: not all variables bound ORA-06512 [message #597793 is a reply to message #597779] Tue, 08 October 2013 08:30 Go to previous message
Lalit Kumar B
Messages: 3122
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Tue, 08 October 2013 18:05
But I already have assigned specific value to the particular bind varibale .
like p_empno :=7839; p_deptno:=10;

It could use that but not doing may because of it is not regular varibale and that is why it is called as bind varibale. Smile





You need to know few things first, BINDING BY POSITION and BINDING BY NAME.

1. The execute immediate and ref cursor statements only supports binding by position. So, for any dynamic SQL, you must specify a exact number of bind variables as it needs placeholders for the bind values per position. So if a bind variable (or multiple bind variables) is used "n" times, you need "n" values in the statement for binding.

2. DBMS_SQL supports binding by name. This is a more flexible option as you only need to bind a variable once even if it is used "n" times in the dynamic statement. It gives the flexibility of dynamic number of bind variables.

Regards,
Lalit
Previous Topic: CREATE TABLE ... DEFAULT TABLESPACE FOR COLUMNS ...
Next Topic: How to multiplied count in SQL
Goto Forum:
  


Current Time: Sat Dec 03 00:58:29 CST 2016

Total time taken to generate the page: 0.09710 seconds