为什么语法execute immediate 一直报错 ？求解释~

Question

set serveroutput on;declare l_cnt varchar2(2000); v_sql varchar2(4000); v_tablename varchar2(2000); v_sqll varchar2(4000);cursor cursor_jsdx is select 'select count(*) from ' || table_name || ' where BANK_ACC=''62238402322222165''',table_name from user_tab_columns where column_name='BANK_ACC'; begin open cursor_jsdx; Loop fetch cursor_jsdx into v_sql,v_tablename; exit when cursor_jsdx%notfound; execute immediate v_sql into l_cnt; if l_cnt >0 then v_sqll:='update ' || v_tablename || ' set BANK_ACC=''370283199010060039'' where BANK_ACC=''62238402322222165'''; dbms_output.put_line(v_sqll); execute immediate v_sqll; end if; end loop; Close cursor_jsdx; end;/就这段sql，在oracle11G正常，在10G就报ORA-00933: SQL command not properly endedORA-06512: at line 12 （execute immediate v_sql into l_cnt;就是这句）

Answer 1

没看出有什么问题，我在Oracle10g中 改动来执行没问题。你可以试着打印v_sql看这个拼凑的sql语句有什么问题。就像这样：

set serveroutput on;declare l_cnt    varchar2(2000); v_sql  varchar2(4000); v_tablename varchar2(2000); v_sqll varchar2(4000);  cursor cursor_jsdx is select 'select count(*) from ' || table_name || ' where BANK_ACC=''62238402322222165''',table_name from user_tab_columns where column_name='BANK_ACC';  begin      open cursor_jsdx;          Loop                      fetch cursor_jsdx into v_sql,v_tablename;                        exit when cursor_jsdx%notfound;                 dbms_output.put_line(v_sql);    --调试打印v_sql                execute immediate v_sql into l_cnt;                             if l_cnt >0 then                           v_sqll:='update ' || v_tablename || ' set BANK_ACC=''370283199010060039'' where BANK_ACC=''62238402322222165''';                      dbms_output.put_line(v_sqll);                       execute immediate v_sqll;          end if;                end loop;             Close cursor_jsdx;    end;/

Answer 2

cursor cursor_jsdx is select 'select count(*) from ' || table_name || ' where BANK_ACC=''62238402322222165''',table_name from user_tab_columns where column_name='BANK_ACC';
这句有问题
BANK_ACC=''62238402322222165''',table_name from user_tab_columns where column_name='BANK_ACC';
这部分，BANK_ACC=''62238402322222165'''后你加table_name是什么意思？
而且v_tablename你疑似没赋值吧？