dynamic sql with quotes and execute immediate

Question

I get tripped up using dynamic sql and quotes. When I dbms_output the sql_stmt, it outputs valid/working code. How should the sql_stmt := line be written so that I can dynamically execute it? I have tried ":1 using" bind variable syntax as well with no luck.

The point of this code is to gather stats for a subset of tables.

set serveroutput on
--create this test table for working example.
--create table test3 as select table_name from user_tables where rownum <= 5;

declare
    sql_stmt varchar2(500);
    begin
        for rec in (select table_name from test3)
        loop 
            sql_stmt := 'exec dbms_stats.gather_table_stats (''SCOTT'',''' || rec.table_name || ''')';
            dbms_output.put_line(sql_stmt);
            execute immediate sql_stmt;  -- <---Error is here---
        end loop;
    end;

The errors I get from the execute immediate sql_stmt; line is: ORA-00900: invalid SQL statement ORA-06512: at line 8

mustaccio mustaccio · Accepted Answer · 2013-06-05T18:05:45

EXEC is an SQL*Plus command. You may want to wrap the call to DBMS_STATS.GATHER_TABLE_STATS in an anonymous block instead if you insist on using dynamic SQL.

However, you should be able to call the procedure directly, like so:

declare
sql_stmt varchar2(500);
begin
    for rec in (select table_name from test3)
    loop 
        dbms_stats.gather_table_stats ('SCOTT',rec.table_name);
    end loop;
end;

dynamic sql with quotes and execute immediate

2 Answers