执行`EXECUTE IMMEDIATE` Oracle 语句获取错误
我是 Oracle 的新手.当我执行以下语句时
I am NewBie to Oracle. When I Execute Following Statement
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM DUAL;';
END;
/
我有错误
从第 2 行开始出错 - BEGIN EXECUTE IMMEDIATE'从双中选择 *;';结尾;
Error starting at line : 2 in command - BEGIN EXECUTE IMMEDIATE 'SELECT * FROM DUAL;'; END;
错误报告 - ORA-00911:无效字符 ORA-06512:在第 2 行00911. 00000 - 无效字符"*原因:标识符不能以任何 ASCII 字符开头，除了字母和数字.$#_ 也允许在第一个之后特点.双引号括起来的标识符可能包含双引号以外的任何字符.替代报价(q'#...#') 不能使用空格、制表符或回车作为分隔符.对于所有其他上下文，请参阅 SQL 语言参考手册.*动作:
Error report - ORA-00911: invalid character ORA-06512: at line 2 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action:
推荐答案
问题在于'SELECT * FROM DUAL;'中的;字符.
来自文档:
execute_immediate_statement ::=
EXECUTE_IMMEDIATE dynamic_string
{
INTO { define_variable [, define_variable ...] | record_name }
| BULK COLLECT INTO { collection_name [, collection_name ...] | :host_array_name }
}
[ USING [ IN | OUT | IN OUT ] bind_argument
[, [ IN | OUT | IN OUT ] bind_argument] ... ] [ returning_clause ] ;
... 其中 dynamic_string 是(强调我的):
... where dynamic_string is (emphasis mine):
表示单个 SQL 的字符串文字、变量或表达式语句或PL/SQL块.它必须是 CHAR 或 VARCHAR2 类型，而不是NCHAR 或 NVARCHAR2.
A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2.
因为除非您将它们包含在单个 PL/SQL 块中，否则它不会接受多个语句，所以不需要 ; 分隔符.
Since it won't accept multiple statements unless you enclose them in a single PL/SQL block, the ; separator is not expected.
使用 EXECUTE IMMEDIATE 语句有更好的解释在 PL/SQL 中:
在动态字符串中构造单个 SQL 语句时，不要在引号内的末尾包含一个分号 (;).什么时候构造一个 PL/SQL 匿名块，包括分号每个 PL/SQL 语句的结尾和匿名块的结尾；字符串末尾将有一个分号文字，另一个跟在右单引号之后.
When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end inside the quotation mark. When constructing a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block; there will be a semicolon immediately before the end of the string literal, and another following the closing single quotation mark.
相关文章