Execute Immediate
今天诊断了一起 ORA-00905 故障，起因由PL/SQL程序中的一段execute immediate代码引起的。
先看一下execute immediate的语法：
execute immediate 'sql';
execute immediate 'sql_select' into var_1, var_2;
execute immediate 'sql' using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2;
execute immediate 'sql_select' into var_1, var_2 using [in|out|in out] bind_var_1, [in|out|in out] bind_var_2;
execute immediate 'sql' returning into var_1;
execute immediate 'sql' bulk collect into indexed_var;在SQL*Plus中测试：
SQL> var myvar number;
SQL> print myvar
MYVAR
---------
SQL> begin
2 execute immediate 'select count(*) into ' || :myvar || ' from dual';
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 2
SQL> begin
2 execute immediate 'select count(*) ' || into :myvar || ' from dual';
3 end;
4 /
execute immediate 'select count(*) ' || into :myvar || ' from dual';
*
ERROR at line 2:
ORA-06550: line 2, column 41:
PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
( - + case mod new null
avg
count current max min prior sql stddev sum variance execute
forall merge time timestamp interval date
pipe
The symbol “INTO” was ignored.
SQL> begin
2 execute immediate ’select count(*) from dual’ into :myvar;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print myvar;
MYVAR
———
1
需要留心的是，如果在包体中写出
execute immediate 'select field into ' || var1 || ' from table';之类的语句，是不会提示错误的。但在运行时会提示expression错误。测试环境：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production