ORA-01008: not all variables bound
cmovvaNov 15 2011 — edited Nov 16 2011Dear all,
Could you please review the following example 1. Not sure where the problem is. Isn't supported by oracle! Thanks in advance for the help!
DB version: 10g rel2
Example 1: Does not work - Using string. Gives an error "ORA-01008: not all variables bound"
DECLARE
v_opr_sql varchar2(4000);
v_table_opr varchar2(60) := 'CM_DEPT';
v_col_string varchar2(60) := 'DEPTNO,DNAME';
v_values_string varchar2(60) := ':1,:2';
v_using_string varchar2(256):= '10,'||'''ACCOUNTING''';
BEGIN
v_opr_sql := 'INSERT INTO '||v_table_opr||' '||
'('||v_col_string||')'||' '||
'VALUES'||' '||'('||v_values_string||')';
EXECUTE IMMEDIATE v_opr_sql using v_using_string;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
Example 2: Works with literals
DECLARE
v_opr_sql varchar2(4000);
v_table_opr varchar2(60) := 'CM_DEPT';
v_col_string varchar2(60) := 'DEPTNO,DNAME';
v_values_string varchar2(60) := ':1,:2';
BEGIN
v_opr_sql := 'INSERT INTO '||v_table_opr||' '||
'('||v_col_string||')'||' '||
'VALUES'||' '||'('||v_values_string||')';
EXECUTE IMMEDIATE v_opr_sql using 10, 'ACCOUNTING';
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
Example 3: Works with variables
DECLARE
v_opr_sql varchar2(4000);
v_table_opr varchar2(60) := 'CM_DEPT';
v_col_string varchar2(60) := 'DEPTNO,DNAME';
v_values_string varchar2(60) := ':1,:2';
v_deptno number := 10;
v_dname varchar2(60) := 'ACCOUNTING';
BEGIN
v_opr_sql := 'INSERT INTO '||v_table_opr||' '||
'('||v_col_string||')'||' '||
'VALUES'||' '||'('||v_values_string||')';
EXECUTE IMMEDIATE v_opr_sql using v_deptno, v_dname;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
Thanks
cmovva