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

问题描述

我是Oracle的NewBie.当我执行以下语句

I am NewBie to Oracle. When I Execute Following Statement

BEGIN
 EXECUTE IMMEDIATE  'SELECT * FROM DUAL;';
END;
 /

我遇到错误

从命令第2行开始的错误-立即开始执行 'SELECT * FROM DUAL;';结尾;

Error starting at line : 2 in command - BEGIN EXECUTE IMMEDIATE 'SELECT * FROM DUAL;'; END;

错误报告-ORA-00911:无效 字符ORA-06512:在第2行 00911. 00000-无效字符" *原因:标识符不得以除ASCII以外的任何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屋！