两个带有 begin 和 end 的 PLSQL 语句单独运行良好但不能一起运行？
只是想知道是否有人可以帮助解决这个问题，我有两个用于更改表（添加额外字段）的 PLSQL 语句，它们如下：
-- Make GC_NAB field for Next Action By Dropdown
begin
if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))';
elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or
'VARCHAR2' = 'VARCHAR2' then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))';
else
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)';
end if;
commit;
end;
-- Make GC_NABID field for Next Action By Dropdown
begin
if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))';
elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or
'NUMBER' = 'VARCHAR2' then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())';
else
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)';
end if;
commit;
end;
当我分别运行这两个查询时，没有问题。然而，当如上所示一起运行时，Oracle 在启动第二条语句时给我一个错误：
Error report:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "BEGIN"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
我假设这意味着第一个语句没有正确终止...是否有什么我应该在语句之间放入的内容它工作正常吗？
Just wondering if anyone can help with this, I have two PLSQL statements for altering tables (adding extra fields) and they are as follows:
-- Make GC_NAB field for Next Action By Dropdown
begin
if 'VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')>0 then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))';
elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) or
'VARCHAR2' = 'VARCHAR2' then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))';
else
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)';
end if;
commit;
end;
-- Make GC_NABID field for Next Action By Dropdown
begin
if 'NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')>0 then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))';
elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) or
'NUMBER' = 'VARCHAR2' then
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())';
else
execute immediate 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)';
end if;
commit;
end;
When I run these two queries separately, there are no problems. However, when run together as shown above, Oracle gives me an error when it starts the second statement:
Error report:
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "BEGIN"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I'm assuming that this means the first statement is not terminated properly... is there anything I should put in between the statements to make it work properly?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Oracle 一次可以获取一个 SQL 语句或 PL/SQL 匿名块。 （与 SQL Server 不同，SQL Server 可以一次排除一批。）因此，您有几个选择。
将两个匿名块包装在另一个匿名块中：
<前><代码>开始
-- 为下拉列表中的下一步操作创建 GC_NAB 字段
开始
如果 'VARCHAR2' = 'NUMBER' 且长度('VARCHAR2')>0 且长度('')>0 则
立即执行 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10, ))';
elsif ('VARCHAR2' = 'NUMBER' and length('VARCHAR2')>0 and length('')=0) 或
'VARCHAR2' = 'VARCHAR2' 那么
立即执行 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2(10))';
别的
立即执行 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NAB VARCHAR2)';
结束如果；
犯罪;
结尾;
-- 为下拉列表中的下一步操作创建 GC_NABID 字段
开始
如果 'NUMBER' = 'NUMBER' 且长度('NUMBER')>0 且长度('')>0 则
立即执行 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER(, ))';
elsif ('NUMBER' = 'NUMBER' and length('NUMBER')>0 and length('')=0) 或
'NUMBER' = 'VARCHAR2' 那么
立即执行 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER())';
别的
立即执行 'alter table "SERVICEMAIL6"."ETD_GUESTCARE" add(GC_NABID NUMBER)';
结束如果；
犯罪;
结尾;
结尾;
告诉您用来将 PL/SQL 提交到 Oracle 的工具分别发送这两个块。如何做到这一点将取决于工具。在 SQL*PLUS 中，单独一行上的
/将完成此操作：Oracle can take one SQL statement or PL/SQL anonymous block at a time. (Unlike SQL Server that can except a batch at a time.) So, you have a couple of options.
Wrap the two anonymous blocks within another anonymous block:
Tell the tool you are using to submit the PL/SQL to Oracle to send the two block seperately. How to do this will be tool specific. In SQL*PLUS, a
/on a line by itself will accomplish this:是的，您需要在每个
end;后面的行上添加斜杠/。Yes, you need a slash
/on the line after eachend;.可以通过两种方式实现：
1）在 pl/sql 文件中分别运行两个块。
2）在主块中，您可以通过以下方式运行两个 sbu 块：
one can achieve in two ways
1) run the two blocks separately in pl/sql file.
2) in main block you can run two sbu-blocks in this way :