如何解决存储过程在通过EXECUTE IMMEDIATE按字符串执行时不执行dbms_stats.gather_tabl

Question

在我的存储过程中，我使用"立即执行"；执行字符串格式的语句，但这不会生成结果。我在下面添加了过程：

CREATE OR REPLACE PROCEDURE sp_TEST(tablenamestring Varchar2) IS
ownerName VARCHAR2(5000);
sqlString VARCHAR2(2000);
BEGIN
FOR ROW_IDX IN ( SELECT trim(regexp_substr(tablenamestring, '[^,]+', 1, LEVEL)) TABLE_NAME FROM 
dual
CONNECT BY LEVEL <= regexp_count(tablenamestring, ',')+1)
LOOP
select owner INTO ownerName  from ALL_OBJECTS where object_name = ROW_IDX.TABLE_NAME;
sqlString :='Exec dbms_stats.gather_table_stats(ownname => ''' || ownerName ||''', tabname =>'''||ROW_IDX.TABLE_NAME ||''', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => '' FOR ALL COLUMNS SIZE AUTO'' , cascade => TRUE, DEGREE=>4)';
--EXECUTE IMMEDIATE  sqlString;
dbms_output.put_line(sqlString);
END LOOP;
END;

我评论了--EXECUTE IMMEDIATE sqlString；并且刚刚检查了dbms_output_put_line显示的sqlstring。SQL String如下所示，并且它执行成功。

Exec dbms_stats.gather_table_stats(ownname => 'NPSQL11', tabname =>'Customer', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ' FOR ALL COLUMNS SIZE AUTO' , cascade => TRUE, DEGREE=>4)

如果我取消注释EXECUTE IMMEDIATE sqlString；并且执行该过程，我无法执行上述查询。我得到的错误如下所示：

ORA-00900: invalid SQL statement
ORA-06512: at NPSQL11.sp_TEST", line 12
ORA-06512: at NPSQL11.sp_TEST, line 12
ORA-06512: at line 2
begin
sp_TEST('Customer');
end;

我是不是错过了什么？EXECUTE IMMEDIATE用于创建表字符串。

Answer 1

EXEC是一个SQL*Plus命令，而不是PL/SQL关键字。

尝试用BEGIN ...; END;替换EXEC ...