在Oracle中将字符串作为查询运行 [英] run string as query in oracle

问题描述

我在Oracle中遇到了一个小问题.我尝试创建一个序列来在已经有数据的表中生成ID.我尝试使用以下匿名块.

i got a little problem in Oracle. I try to create a sequence for generating IDs in a table that already has data in it. I try to use the following anonymous block.

declare y varchar2(2000);
BEGIN
  SELECT 'CREATE SEQUENCE ID_SEQ MINVALUE 1 MAXVALUE 9999999999 START WITH ' || (max(ID)+1) || ' INCREMENT BY 1 CACHE 20;' INTO y FROM TEST_TABLE;
  --dbms_output.put_line(y);
  execute immediate y;
end;

我收到以下错误:

Error report:
ORA-00911: invalid character
ORA-06512: at line 5
00911. 00000 -  "invalid character"

如果我执行y变量的值，它将运行完美.我正在使用SQL Developer作为输入接口，并在11g r2 Oracle服务器上工作.我在生成"INCREMENT BY"参数的脚本中发现了类似的代码.有人可以解释我的错误吗?

If I execute the value of the y variable it works perfectly. I'm using SQL Developer as input interface and working on a 11g r2 Oracle server. I found similar codes where 'INCREMENT BY' parameter were script generated. Can someone explain my error?

推荐答案

execute immediate时，运行的命令结尾不应以分号；这是SQL Developer(和 SQL * Plus 中的命令分隔符，和其他客户端)，而不是SQL语句本身的一部分.

When you execute immediate, the command you run should not have a semi-colon on the end; that is a command separator in SQL Developer (and SQL*Plus, and other clients), not part of the SQL statement itself.

SELECT 'CREATE SEQUENCE ID_SEQ MINVALUE 1 MAXVALUE 9999999999 START WITH '
    || (max(ID)+1) || ' INCREMENT BY 1 CACHE 20' INTO y FROM TEST_TABLE;

在纯SQL的示例中显示.不过，这只是为了让您感到困惑，如果您在动态SQL中使用PL/SQL，您仍然需要适合PL/SQL本身的分号-尽管您不需要执行/来直接从Windows中运行它.客户. 其他示例中显示.

This is shown in the examples for plain SQL. Just to help confuse you though, if you are using PL/SQL within the dynamic SQL, you do still need semi-colons appropriate to PL/SQL itself - though not the executing / you'd use to run it directly from a client. This is shown in other examples.

这篇关于在Oracle中将字符串作为查询运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！