为什么使用 EXECUTE IMMEDIATE 运行此查询会导致它失败?

2021-12-24 00:00:00 sql oracle plsql dynamic-sql ora-00911

我正在编写一个需要动态生成一些查询的 PL/SQL 过程,其中一个涉及使用作为参数的查询的结果创建临时表.

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.

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 抛出 ORA-00911:无效字符.如果我手动运行创建的查询,它会正确运行.在这一点上,我能够确定导致问题的原因.

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.

推荐答案

尽量去掉;"从您立即执行的字符串内部.

Try to lose the ";" from inside the string that you Execute Immediate.

EXECUTE IMMEDIATE  'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ')';

相关文章