oracle execute immediate未执行
我是Oracle (11gr2)的新手，我有以下脚本：
BEGIN
DECLARE
source varchar2(1);
BEGIN
dbms_output.enable;
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW SP_AD;';
SELECT SOURCE INTO source FROM map_switch WHERE ROWNUM = 1;
IF source = 'A'
THEN
EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
EXECUTE IMMEDIATE 'RENAME TABLE SP_AD_A TO SP_AD;';
ELSE
EXECUTE IMMEDIATE 'DROP TABLE SP_AD_A;';
EXECUTE IMMEDIATE 'RENAME TABLE SP_AD_B TO SP_AD;';
END IF;
COMMIT WORK;
dbms_output.put_line('SP_AD table issue fixed');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
ROLLBACK WORK;
END;
END;
END;
/本质上，它决定删除哪个表，然后删除视图并重命名另一个表。
如果我单独运行这些语句，它会运行得很好，但在上面的脚本中，它会返回procedure executed successfully，但什么都没有执行。
我怀疑由于某种奇怪的原因，它正在回滚，但我正在犹豫是否在没有回滚的情况下执行它(这些表有超过300,000条记录)。
有人能告诉我哪里出了问题吗?还有，我的异常块有什么问题吗？
Stack Overflow用户
发布于 2013-11-08 03:38:48
正如评论者所指出的，有几个原因导致你的代码不能像预期的那样工作。
首先，不要在传递给EXECUTE IMMEDIATE的字符串中使用分号，因为这样做会给出ORA-00911“无效字符”错误：
SQL> BEGIN
2 EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2运行此命令后，您可以验证表是否仍然存在：
SQL> SELECT * FROM SP_AD_B;
no rows selected(我没有您的表SP_AD_B，所以我只创建了一个名为SP_AD_B的表，其中只有一个整数列。我没有费心在其中放入任何数据。)
如果您删除字符串内部的分号，而不是字符串外部的分号，它将起作用：
SQL> BEGIN
2 EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B';
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SP_AD_B;
SELECT * FROM SP_AD_B
*
ERROR at line 1:
ORA-00942: table or view does not exist现在表已经不在了，我们在尝试查询它时会遇到错误。
希望这能让你修复你的脚本，这样它就能正常工作，并删除相关的表。
但是为什么在输出消息中没有得到任何有用的信息呢？那么，让我们重新创建SP_AD_B表，并重新引入分号，然后再次尝试删除该表，但使用的EXCEPTION处理程序与您的类似：
SQL> BEGIN
2 EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
3 EXCEPTION
4 WHEN OTHERS THEN
5 dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
6 END;
7 /
Exception, rolling back transaction, SP_AD not resolved.
PL/SQL procedure successfully completed.在本例中，我们收到一条错误消息，告诉我们出了问题，所以表没有被删除。但是哪里出了问题呢？Oracle可以报告数以千计的错误，在不知道错误消息的情况下，很难猜测问题是什么。
这里有许多你可以采用的方法。首先，您可以将错误消息以SQLERRM格式写入dbms_output
SQL> BEGIN
2 EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
3 EXCEPTION
4 WHEN OTHERS THEN
5 dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
6 dbms_output.put_line('Error message was: ' || SQLERRM);
7 END;
8 /
Exception, rolling back transaction, SP_AD not resolved.
Error message was: ORA-00911: invalid character
PL/SQL procedure successfully completed.如果愿意，还可以使用dbms_utility.format_error_backtrace将当前堆栈跟踪作为字符串返回。这可能会帮助您找出错误的来源。
或者，您可以重新引发异常。在EXCEPTION处理程序中单独使用RAISE会重新引发当前异常：
SQL> BEGIN
2 EXECUTE IMMEDIATE 'DROP TABLE SP_AD_B;';
3 EXCEPTION
4 WHEN OTHERS THEN
5 dbms_output.put_line('Exception, rolling back transaction, SP_AD not resolved.');
6 RAISE;
7 END;
8 /
Exception, rolling back transaction, SP_AD not resolved.
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 6然而，考虑到您的EXCEPTION处理程序实际上并没有做任何有帮助的事情，最好的方法很可能就是完全去掉它。
您的异常处理程序无法实现任何功能，因为您无法提交或回滚CREATE、ALTER、DROP或TRUNCATE等DDL语句。这些语句中的每一个在运行之前和之后都会立即发出一个COMMIT。如果DROP成功但RENAME失败，则无法通过回滚事务来恢复删除的表。我建议去掉COMMIT WORK和ROLLBACK WORK语句。
最后，评论员Jeffrey Kemp注意到了这一行：
SELECT SOURCE INTO source FROM map_switch WHERE ROWNUM = 1;这会将表map_switch的某个任意行中的列SOURCE的值赋给一个名为source的变量。它可以是任何行；因为您没有指定任何排序，所以Oracle可以按照自己喜欢的方式对map_switch的行进行排序。
如果表中只有一行，那么您将返回哪一行就很清楚了。但是，如果是这样的话，为什么要指定ROWNUM = 1？表是否有多行，ROWNUM = 1部分是否只是为了消除“精确获取返回的行数超过请求的行数”的错误？
你最好做一些类似以下的事情：
SELECT SOURCE INTO source
FROM (SELECT SOURCE FROM map_switch ORDER BY some_column)
WHERE ROWNUM = 1;我不知道您的map_switch表中有哪些列，所以我只是使用上面的some_column作为其中一列的占位符。如果可能，请选择具有唯一值的列。
请注意，我们不能简单地执行SELECT ... WHERE ROWNUM = 1 ORDER BY some_column，因为这会在排序之前应用ROWNUM = 1子句，而且对单行进行排序也没有太多意义，因为它只有一个返回顺序。
https://stackoverflow.com/questions/19820705
复制相似问题
腾讯云开发者
