博客探讨了在Oracle 10g环境中，动态SQL执行时遇到的问题。示例1中，尝试使用字符串变量插入数据导致'ORA-01008: not all variables bound'错误。示例2通过直接使用字面量成功插入数据，而示例3通过变量赋值方式也实现了成功插入。问题在于动态SQL中的参数绑定方式。
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
扫一扫
4113
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
