oracle 为什么使用 EXECUTE IMMEDIATE 运行此查询会导致它失败？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/885304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does running this query with EXECUTE IMMEDIATE cause it to fail?
提问by GameFreak
I am writing a PL/SQL procedure that needs to to dynamically generate some queries, one of which involves creating a temporary table using results from a query taken as a parameter.
我正在编写一个 PL/SQL 过程，它需要动态生成一些查询，其中一个涉及使用作为参数的查询的结果创建临时表。
CREATE OR REPLACE PROCEDURE sqlout(query IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ');';
END;
It compiles correctly, but even with very simple queries such as with:
它编译正确，但即使使用非常简单的查询，例如：
BEGIN
sqlout('SELECT * FROM DUAL');
END;
IT throws ORA-00911: invalid character. If I run the created query manually it runs correctly. At this point I am able to determine what is causing the problem.
它抛出ORA-00911: invalid character。如果我手动运行创建的查询，它会正确运行。在这一点上，我能够确定导致问题的原因。
回答by Petros
Try to lose the ";" from inside the string that you Execute Immediate.
尽量去掉“;” 从您立即执行的字符串内部。
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';
