ORACLE 在立即执行中批处理 DDL 语句
我正在尝试在一个 Execute Immediate 语句中运行多个 ddl 语句。 我认为这会很简单，但看来我错了。
想法是这样的：
declare v_cnt number;
begin
select count(*) into v_cnt from all_tables where table_name='TABLE1' and owner = 'AMS';
if v_cnt = 0 then
execute immediate 'CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL) ALTER TABLE TABLE1 ADD (MYVAL2 NVARCHAR2(10))';
end if;
end;
但这会导致错误
ORA-00911: 无效字符 ORA-06512: 在第 10 行
如果我自己执行批处理中的每个语句，它们都可以正常运行。 如果我接受这个语句并执行它，它将正常运行（在两个语句之间使用 ; ）。 如果我删除 ; 在语句之间，我收到有关无效选项的不同错误
该计划是我将能够构建一个表，导出该表的表模式（包括所有更改语句），然后作为安装的一部分针对另一个系统运行批处理/更新过程。
那么，如何在单个立即执行中批处理这些 DDL 语句呢？ 或者有更好的方法来做我需要的事情吗？
我必须承认，我是一个 Oracle 新手。 感谢大家的耐心等待。
I'm trying to run multiple ddl statements within one Execute Immediate statement.
i thought this would be pretty straight forward but it seems i'm mistaken.
The idea is this:
declare v_cnt number;
begin
select count(*) into v_cnt from all_tables where table_name='TABLE1' and owner = 'AMS';
if v_cnt = 0 then
execute immediate 'CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL) ALTER TABLE TABLE1 ADD (MYVAL2 NVARCHAR2(10))';
end if;
end;
however this results in an error
ORA-00911: invalid character
ORA-06512: at line 10
Each of the statements within the batch run fine if i execute them by themselves. and if i take this statement and execute it, it will run fine (with the ; between the 2 statements). If i remove the ; between statements i get a different error about invalid option
the plan is that i'll be able to build a table, export the table schema for this table including all it's alter statements, and then run the batch against another system as part of an install/update process.
So, how do i batch these DDL statements within a single execute immediate? Or is there a better way to do what i'm needing?
I'm a bit of a Oracle newb, i must admit. Thank you all for your patience.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
分号不是 Oracle SQL 语法的一部分。 SQL*Plus 和其他客户端工具使用分号来表示 SQL 语句的结束，但服务器看不到它。
我们可以强制 SQL*Plus 将分号传递给数据库：
如果我接受此语句并执行它，它将正常运行（在 2 个语句之间使用 ; ） 您正在使用的客户端工具，将其分成两次对数据库的调用。
因此，我认为不可能在立即执行中传递多个语句。
我想人们可以使用包含匿名 PL/SQL 块的字符串调用立即执行，并在其中单独调用立即执行...但我不知道这样做的意义何在。 ;)
The semicolon is not part of Oracle's SQL syntax. SQL*Plus and other client side tools use semicolon to signal the end of a SQL Statement, but the server doesn't see it.
We can force SQL*Plus to pass the semicolon to the DB:
If i take this statement and execute it, it will run fine (with the ; between the 2 statements) The client tool you are using, breaks it into two calls to the DB.
So, I don't think it is possible to pass multiple statements inside an execute immediate.
I suppose one could call execute immediate with a string containing an anonymous PL/SQL block, with individual calls to execute immediate inside it ... and I don't know what the point of doing that would be. ;)
为什么需要一个 EXECUTE IMMEDIATE 调用？ 当然只需调用 2 次就可以了吗？
请记住，每个 DDL 语句都包含一个隐式 COMMIT，因此将其作为单个调用执行没有并发优势。
另外，为什么不在第一次调用时正确设置表格呢？ 您可以执行...
CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL, MYVAL2 NVARCHAR2(10))
...而不是需要 2 次调用。
另外，您是否看过 DBMS_METADATA...它可以为您生成表等对象的 DDL。
Why do you need a single EXECUTE IMMEDIATE call? Surely just do it as 2 calls?
Bear in mind that each DDL statement contains an implicit COMMIT, so there's no concurency benefit to doing it as a single call.
Also, why not just set up the table correctly in the first call? You could do...
CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL, MYVAL2 NVARCHAR2(10))
...instead of needing 2 calls.
Also, have you looked at DBMS_METADATA... it can generate DDL for objects such as tables for you.