立即执行中的ORACLE批处理DDL语句 [英] ORACLE Batching DDL statements within a Execute Immediate

问题描述

我正在尝试在一个立即执行"语句中运行多个ddl语句. 我以为这会很简单，但似乎我弄错了.

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.

想法是这样的:

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行

ORA-00911: invalid character ORA-06512: at line 10

如果我自己执行它们，则批处理中的每个语句都运行良好.如果我接受此语句并执行它，它将运行良好(在2条语句之间使用;).如果我删除;在语句之间，我收到关于无效选项的另一个错误

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

计划是，我将能够构建一个表，导出该表的表模式(包括所有的alter语句)，然后在安装/更新过程中针对另一个系统运行该批处理.

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.

那么，如何在一个立即执行中批处理这些DDL语句?还是有更好的方法来做我需要的?

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.

推荐答案

为什么需要一个EXECUTE IMMEDIATE调用?当然可以按2个电话通话吗?

Why do you need a single EXECUTE IMMEDIATE call? Surely just do it as 2 calls?

请记住，每个DDL语句都包含一个隐式COMMIT，因此将其作为单个调用进行并没有好处.

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

创建表TABLE1(VALUE VARCHAR2(50)NOT NULL，MYVAL2 NVARCHAR2(10))

CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL, MYVAL2 NVARCHAR2(10))

...不需要打2个电话.

...instead of needing 2 calls.

另外，您是否看过DBMS_METADATA ...它可以为您的对象(例如表)生成DDL.

Also, have you looked at DBMS_METADATA... it can generate DDL for objects such as tables for you.

这篇关于立即执行中的ORACLE批处理DDL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！