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.
EXEC is a SQL*Plus command, not a PL/SQL keyword.
Try replacing EXEC ...
with BEGIN ...; END;