If some objects are still invalid -- that means there is some error with them -- use the "show errors " command in sqlplus to diagnose.select object_type,count(*) from user_objects where status = 'INVALID' 2 group by object_type; OBJECT_TYPE COUNT(*) ------------------ ---------- PACKAGE BODY 4 PROCEDURE 61 VIEW 3 next, as sys exec dbms_utility.compile_schema( 'ACCOUNTS' ); PL/SQL procedure successfully completed.
Tom, I have noticed that using Powerbuilder application to access an invalid object does not automatically recompile the object, it gave a ora-20003 error instead.
After I compiled it manually, the application ran fine. cause Sybase doesn't know how to program against Oracle?
Seriously, it is because they are trying to describe an invalid object, when they encounter the 20003, they should simply PARSE: begin OBJECT; end; regardless of what object is -- that will auto-compile it (a describe will not). You could file an enhancement request with them to have them do it. Tom, I tried to understand what's happening in here: I use one dynamic script to recompile all invalid pkg/fn/procedure under one dba account, and interesting thing is, one of procedure in schema1 turned to invalid after same name of procedure in schema2 didn't recompiled successfully due to less privilege on common tables in schema3.
(procedure in schema1 will be valid if recompiled individually).
after I granted privilege from schema3, both procedures under schema1/2 recompiled successfully. Is it a good exercise to recompile invalid objects under dba accounts for all schema? I don't bother compiling objects, no -- they recompile themselves when invoked.
However these two schemas didn't call each other, other than they access to same table in schema3. But if you give me a test case with create users, etc etc etc that shows the issue you hit, I'll explain why. Regards, Arash select object_type, object_name, status 2 from user_objects 3 where status = 'INVALID' 4 order by object_type, object_name 5 / OBJECT_TYPE OBJECT_NAME STATUS --------------- ------------------------------ ---------- PROCEDURE P INVALID i cannot reproduce and by "begin OBJECT; end;" what I mean is -- all PB needs to do is parse a string 'begin p; end;' since P is the "object" in question here.What you have above is insufficient to reproduce (it doesn't work the way you describe).... Hi Tom, The issue that I have is, there is function F1 which is using table T1, T2, T3. But when I want to use it in application which is PB 8 , it fails and I have to recompile it manually, then it works. the block I generally use it: begin if (1=0) then p; end if; end; that'll cause p to compile (if it can) but most certainly would never actually execute it by accident.They will fix themselves as they are executed or accessed.I never worry about some invalid objects -- you'll almost always have some somewhere.You could create a full time job fixing them -- or just let the database do it.In any case: exec dbms_utility.compile_schema( 'SCOTT' ) would compile all of the invalid objects owned by SCOTT (note: must use UPPER case on the schema name).