Need Assistance with PL/SQL Syntax

Question

Can someone please pinpoint the syntax error in the below code.

DECLARE
    plsql_blk   VARCHAR2 (250);
begin
  plsql_blk := 'begin DBMS_STATS.DELETE_TABLE_STATS ('||''''|| OWNER ||''''||','||''''|| TABLE_NAME ||''''||');'
                from dba_tables
                where owner = 'SYSADM'
                and table_name like 'AS_TAO%'
                or table_name like 'AS_BP_XLBP_TAO%'
                order by table_name; end;';
  execute immediate plsql_blk;            

end;
/

Thanks in advance.

Answer 1

That would be "too many errors" error (only if it existed).

• you can't execute a procedure which "looks like" a SELECT statement. Where did FROM clause come from (as well as the rest of that piece of code)?
• if you are using OR among other conditions, you have to enclose it into parenthesis, otherwise you'll get false result
• I didn't count them, but it looks as if you got lost in that many single quotes. Instead of that, consider using the q-quoting mechanism which helps A LOT

---

Let's try to make it work. I don't have your users nor tables so I'll do that in Scott's sample schema.

First, gather statistics (otherwise there's nothing to delete):

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP');

PL/SQL procedure successfully completed.

SQL>

This is a procedure which shows some statistics (why a procedure? So that I wouldn't have to copy/paste the whole code once again at the end of this demonstration):

SQL> create or replace procedure p_getstat(par_table in varchar2)
  2  is
  3    l_numrows   number;
  4    l_numblks   number;
  5    l_avgrlen   number;
  6    l_cachedblk number;
  7    l_cachehit  number;
  8  begin
  9    dbms_stats.get_table_stats
 10      (ownname   => 'SCOTT',
 11       tabname   => dbms_assert.simple_sql_name(par_table),
 12       numrows   => l_numrows,
 13       numblks   => l_numblks,
 14       avgrlen   => l_avgrlen,
 15       cachedblk => l_cachedblk,
 16       cachehit  => l_cachehit
 17      );
 18    dbms_output.put_line(par_table || ' has ' || l_numrows || ' row(s)');
 19  end;
 20  /

Procedure created.

Let's try it:

SQL> set serveroutput on
SQL>
SQL> exec p_getstat('EMP');
EMP has 14 row(s)

PL/SQL procedure successfully completed.

SQL>

Now, your procedure, fixed. You overcomplicated it by putting everything into dynamic SQL. Loop through tables you're interested in, but execute only the necessary part in dynamic fashion:

SQL> create or replace procedure p_delstat (par_owner in varchar2)
  2  is
  3    l_str varchar2(200);
  4  begin
  5    for cur_r in (select owner, table_name
  6                  from all_tables
  7                  where owner = dbms_assert.simple_sql_name(par_owner)
  8                    and (   table_name like 'EMP%'
  9                         or table_name like 'AS_BP_XLBP_TAO%'
 10                        )
 11                  order by table_name
 12                 )
 13    loop
 14      l_str := 'begin dbms_stats.delete_table_stats(:a, :b); end;';
 15      execute immediate l_str using cur_r.owner, cur_r.table_name;
 16    end loop;
 17  end;
 18  /

Procedure created.

Does it work?

SQL> exec p_delstat('SCOTT');

PL/SQL procedure successfully completed.

SQL>

Seems so; at least, didn't raise any error. Let's check whether statistics for previously mentioned EMP table still exist:

SQL> exec p_getstat('EMP');
BEGIN p_getstat('EMP'); END;

*
ERROR at line 1:
ORA-20000: Unable to get values for table EMP
ORA-06512: at "SYS.DBMS_STATS", line 7688
ORA-06512: at "SCOTT.P_GETSTAT", line 9
ORA-06512: at line 1


SQL>

Nope, statistics is gone. Looks like we've done it correctly.

---

Adjust that code so that it works in your database, with your user(s) and your table(s).