oracle-databaseplsqlexecute-immediate

How to resolve issue of stored proc not executing dbms_stats.gather_table_stats() when executing as it string via EXECUTE IMMEDIATE


In my stored procedure, I am using "EXECUTE IMMEDIATE" to execute statement that is in string format but this is not generating the result. I have added proc below:

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;

I commented on --EXECUTE IMMEDIATE sqlString; and just checked on sqlstring shown by dbms_output.put_line. SQL String is shown below and it executes successfully.

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)

If I uncomment EXECUTE IMMEDIATE sqlString; and execute the procedure, I am not able to execute above query. I am getting error as shown below:

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;

Am I missing somthing? EXECUTE IMMEDIATE used to work on create table string.


Solution

  • EXEC is a SQL*Plus command, not a PL/SQL keyword.

    Try replacing EXEC ... with BEGIN ...; END;