Dear 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