troubleshooting Question

ORA-00911: invalid character while running the dynamic sql

Avatar of gram77
gram77Flag for India asked on
Oracle Database
11 Comments1 Solution4897 ViewsLast Modified:
I have written a simple program to compile all invalid programs in a schema,

This program compiles fine, but on running it gives the error:
Error: -911 : ORA-00911: invalid character

Please let me know how to solve this.

Code...
************************************************************
create or replace
PROCEDURE COMPILE_INVALID AS
BEGIN
dbms_output.put_line('COMPILING.. ');
FOR C1 in (select object_type, object_name
                  from all_objects
                  where status='INVALID'
                      and owner='CONFMAN') LOOP
  dbms_output.put_line(C1.object_name||' ('||C1.object_type||')');                
  IF C1.object_type='PROCEDURE' THEN
      EXECUTE IMMEDIATE 'ALTER PROCEDURE ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='FUNCTION' THEN
      EXECUTE IMMEDIATE 'ALTER FUNCTION ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='INDEX' THEN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='PACKAGE' THEN
      EXECUTE IMMEDIATE 'ALTER PACKAGE ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='PACKAGE BODY' THEN
      EXECUTE IMMEDIATE 'ALTER PACKAGE BODY ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='SYNONYM' THEN
      EXECUTE IMMEDIATE 'ALTER SYNONYM ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='TABLE' THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='VIEW' THEN
      EXECUTE IMMEDIATE 'ALTER VIEW ' || C1.object_name ||' COMPILE;';
  ELSIF C1.object_type='DATABASE LINK' THEN
      EXECUTE IMMEDIATE 'ALTER DATABASE LINK ' || C1.object_name ||' COMPILE;';
  END IF;
END LOOP;
dbms_output.put_line('END..');

EXCEPTION
  WHEN OTHERS THEN
      dbms_output.put_line('Error: '||sqlcode||' : '||sqlerrm);
END COMPILE_INVALID;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros