执行`EXECUTE IMMEDIATE` Oracle 语句获取错误 [英] executing `EXECUTE IMMEDIATE ` Oracle Statement Getting Error

问题描述

我是 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.

这篇关于执行`EXECUTE IMMEDIATE` Oracle 语句获取错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！