[Hint] Invalid objects in the

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 01 Dec 2005 2:19 pm

the query below shows the invalid objects in the ...

Code: Select all
SELECT DISTINCT u.object_name,
       decode(u.object_type,'PACKAGE BODY','PACKAGE',u.object_type) object_type,
       u.created
  FROM user_objects u
WHERE u.status = 'INVALID'
   AND u.object_type IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE')
ORDER BY u.created
and this next compiles the invalid objects.

Code: Select all
DECLARE
  CURSOR c_obj IS
    SELECT DISTINCT u.object_name,
           decode(u.object_type,'PACKAGE BODY','PACKAGE',u.object_type) object_type,
               u.created
      FROM user_objects u
     WHERE u.status = 'INVALID'
         AND u.object_type IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE')
     ORDER BY u.created;
  r_obj NUMBER;
BEGIN
  FOR r_obj IN c_obj LOOP
      BEGIN
      EXECUTE IMMEDIATE 'alter '||r_obj.object_type||' '||r_obj.object_name||' compile';
      dbms_output.put_line(r_obj.object_type||' '||r_obj.object_name||': COMPILADO!' );
      EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('Erro Compilando '||r_obj.object_type||' '||r_obj.object_name);
     END;
   END LOOP;
END;
[] ´ s: wink:
anderson
Location: Toledo - PR

Anderson Nuernberg
---

Poston Fri, 02 Dec 2005 8:16 am

Another way to do this (I think since version 9i) is to use DBMS_UTILITY.

Here is an example:
Code: Select all
exec dbms_utility.compile_schema('SCOTT');
below, is the syntax:
Code: Select all
dbms_utility.compile_schema(
schema         IN VARCHAR2,
compile_all    BOOLEAN DEFAULT TRUE,
reuse_settings BOOLEAN DEFAULT FALSE);
:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 06 Jul 2006 10:30 am

give to use dbms_utility. compile_schema by dblink??
mello
Location: sp



Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests