Regarding PLSQL

Question

What is the problem in below plsql? I am running this code in sql developer.

BEGIN
 FOR x IN (SELECT * FROM dba_tables WHERE owner = 'ABPPMGR_TE2' AND table_name IN
            (select object_name from dba_objects where object_type = 'MATERIALIZED VIEW' and status = 'VALID')
          )
  LOOP
     dbms_stats.gather_table_stats( x.owner, x.table_name); 
  END LOOP;
END;

It gives the error:

ORA-06550: line 2, column 2: PLS-00103: Encountered the symbol " " when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol " " was ignored.

Answer 1

Just tried to see if there are any Non-Ascii characters in your code using below query

select asciistr('BEGIN
 FOR x  IN
 (SELECT *
 FROM dba_tables
 WHERE owner     = ''ABPPMGR_TE2''
 AND table_name IN
   (SELECT object_name
    FROM dba_objects
   WHERE object_type = ''MATERIALIZED VIEW''
   AND status        = ''VALID''
   )
 )
  LOOP
   dbms_stats.gather_table_stats( x.owner, x.table_name);
 END LOOP;
END') from dual;

Did not find any Non-Ascii characters. The code seems correct to me as it executed without any issues on my system.

Answer 2

I think your problem is that you have a "strange" characters on your code, its very common when you copy something from forums or websites.

When I ran your code and I find some character that resembles a space (' ') but arent spaces, and received the same erro that you got, you can run ASCII function to check it.

SELECT ASCII(' ') FROM dual; -- strange character that returns ascii code 160
SELECT ASCII(' ') FROM dual; -- normal space returns 32

Just remove all the strange characters from your code and it will probably work.

Answer 3

I tired it as is and it worked fine for me as well. I will try to include the screenshot.

Best.