Compiling invalid objects using a select statement 2

[SJSFoxPro]

I am trying to write a small script to use in SQL*Plus to compile invalid objects. My schema has invalid objects, however when I run this code, I get the following error: ORA-00900: invalid SQL statement. If I run the SQL statement produced by the "select", it runs fine. What am I overlooking?

DECLARE
cmd VARCHAR2(256);
BEGIN

FOR rec in (select chr(39)||'Alter '||object_type||' '||object_name||' compile'||chr(39) cmd from user_objects where status = 'INVALID')
LOOP
dbms_output.put_line(rec.cmd);
execute immediate rec.cmd;
end loop;

END;
/

 

[eagertotry]

Remove chr(39) from your code. It is adding extra single quotes that are not needed here.

  1  declare
  2  cmd varchar2(256);
  3  begin
  4  for rec in (select 'select * from global_name' cmd from dual)
  5  loop
  6     dbms_output.put_line(rec.cmd);
  7  execute immediate rec.cmd;
  8  end loop;
  9* end;
SQL> /
select * from global_name
 
PL/SQL procedure successfully completed.

 

[SJSFoxPro]

Thank you! I realize why I added that - it was an attempt to resolve another error.

What I find now is this: the first invalid object in my database is a procedure. The procedure has compilation errors, so my code errors on that procedure and doesn't complete (leaving my other invalid objects uncompiled).

Any thoughts for trapping for the error and allowing the loop to continue?

 

[SantaMufasa]

SJS,

I modified your original code to yield the following (which I hope resolves your need to your satisfaction):

set serveroutput on format wrap
DECLARE
    cmd         VARCHAR2(256);
    obj         varchar2(30);
    compile_error	exception;
    pragma exception_init(compile_error,-24344);
    procedure display_error is
        begin
            dbms_output.put_line ('****************************************');
            dbms_output.put_line ('Error(s) during "'||cmd||'":');
            for r in (select 'Line: '||line||', Column: '||position||'...'||text err
                        from user_errors
                       where name = obj) loop
                dbms_output.put_line(r.err);
            end loop;
            dbms_output.put_line ('****************************************');
        end;
BEGIN
    FOR rec in (select 'Alter '
                       ||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
                       ||' '||object_name||' compile' cmd,
                       object_name obj
                  from user_objects where status = 'INVALID') LOOP
       cmd := rec.cmd;
       obj := rec.obj;
       dbms_output.put_line(cmd);
       begin
           execute immediate cmd;
       exception
           when compile_error then
               display_error;
       end;
    end loop;
END;
/

Alter TRIGGER MREPLICATE compile
****************************************
Error(s) during "Alter TRIGGER MREPLICATE compile":
Line: 2, Column: 17...PL/SQL: ORA-04052: error occurred when looking up remote object TESTNEW.T_REGION@YADA.WORLD
ORA-00604: error occurred at recursive SQL level 2

ORA-12154: TNS:could not resolve service name
Line: 2, Column: 5...PL/SQL: SQL Statement ignored
****************************************
Alter PACKAGE VAC compile
****************************************
Error(s) during "Alter PACKAGE VAC compile":
Line: 8, Column: 36...PL/SQL: ORA-00942: table or view does not exist
Line: 8, Column: 22...PL/SQL: SQL Statement ignored
Line: 9, Column: 20...PLS-00364: loop index variable 'R' use is invalid
Line: 9, Column: 17...PL/SQL: Statement ignored
****************************************
Alter PACKAGE VACATION_LIABILITY compile

PL/SQL procedure successfully completed.
*************************************************************************************

Notice that PACKAGE VACATION_LIABILITY successfully recompiled since no error listing follows the recompile command.

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

In fact the order of commands also matters as when you recompile referenced object after a referencing one the "caller" becomes invalid again. You may use DBMS_UTILITY.COMPILE_SCHEMA instead.

Regards, Dima

http://seminihin.narod.ru

 

[SJSFoxPro]

SantaMufasa,

Thank you very much! Your post is most useful. Resolved my issue as well as providing some additional helpful items. Your technical advice on this and many other posts is greatly appreciated.

SJS

 

[carp]

I usually use code similar to SantaMufasa's with a slight modification.

Instead of

(select 'Alter '
||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
||' '||object_name||' compile' cmd,
object_name obj
from user_objects where status = 'INVALID')

I use

(select 'Alter '
||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
||' '||object_name||' compile'||
decode(object_type,'PACKAGE BODY',' BODY',null) cmd,
object_name obj
from user_objects where status = 'INVALID')

This allows me to compile package bodies instead of entire packages if the package spec is not invalid. Compiling the entire package can invalidate other objects; compiling just the body avoids this.

 

[SantaMufasa]

Good call, Dave (Carp). I always like your suggestions. Have a star. (Good to see you back on Tek-Tips, too!)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.