CREATE OR REPLACE PROCEDURE P_COMPILE_INVALID_OBJECTS
AUTHID CURRENT_USER
IS
str_exec VARCHAR2 (200);
total_inv NUMBER;
err_code VARCHAR2 (200);
err_msg VARCHAR2 (200);
BEGIN
/*Author: Alper Özsöyler */
FOR cur_object IN ( SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner)
LOOP
str_exec := 'ALTER ';
IF cur_object.object_type = 'PACKAGE BODY'
THEN
str_exec := str_exec || 'PACKAGE ';
ELSE
str_exec := str_exec || cur_object.object_type || ' ';
END IF;
str_exec :=
str_exec
|| cur_object.owner
|| '.'
|| cur_object.object_name
|| ' COMPILE ';
IF cur_object.object_type = 'PACKAGE BODY'
THEN
str_exec := str_exec || 'BODY ';
END IF;
BEGIN
EXECUTE IMMEDIATE str_exec;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
cur_object.owner
|| '.'
|| cur_object.object_name
|| ' ('
|| cur_object.object_type
|| ') '
|| '-> still invalid!');
err_code := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 200);
INSERT INTO t_cmp_invalid_objects_log
VALUES (s_cpl_inv_objects_error_log.NEXTVAL,
cur_object.object_name,
cur_object.owner,
cur_object.object_type,
err_code || ' -ERROR- ' || err_msg);
COMMIT;
END;
END LOOP;
str_exec := 'select count(*) FROM dba_objects
WHERE status = ''INVALID''';
EXECUTE IMMEDIATE str_exec INTO total_inv;
DBMS_OUTPUT.put_line ('Total invalid number: ' || total_inv);
END;
/
DROP TABLE t_cmp_invalid_objects_log
/
CREATE TABLE t_cmp_invalid_objects_log
(
log_id INT PRIMARY KEY,
object_name VARCHAR2 (100),
object_schema VARCHAR2 (100),
object_type VARCHAR2 (100),
error VARCHAR2 (1000)
)
/
DROP SEQUENCE s_cpl_inv_objects_error_log
/
CREATE SEQUENCE s_cpl_inv_objects_error_log MINVALUE 0
START WITH 0
INCREMENT BY 1
CACHE 5;
Usage:
exec P_COMPILE_INVALID_OBJECTS();
Check error logs:
SQL> show error;
or
SQL> SELECT *
FROM ALL_ERRORS
WHERE OWNER = USER;
AUTHID CURRENT_USER
IS
str_exec VARCHAR2 (200);
total_inv NUMBER;
err_code VARCHAR2 (200);
err_msg VARCHAR2 (200);
BEGIN
/*Author: Alper Özsöyler */
FOR cur_object IN ( SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner)
LOOP
str_exec := 'ALTER ';
IF cur_object.object_type = 'PACKAGE BODY'
THEN
str_exec := str_exec || 'PACKAGE ';
ELSE
str_exec := str_exec || cur_object.object_type || ' ';
END IF;
str_exec :=
str_exec
|| cur_object.owner
|| '.'
|| cur_object.object_name
|| ' COMPILE ';
IF cur_object.object_type = 'PACKAGE BODY'
THEN
str_exec := str_exec || 'BODY ';
END IF;
BEGIN
EXECUTE IMMEDIATE str_exec;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
cur_object.owner
|| '.'
|| cur_object.object_name
|| ' ('
|| cur_object.object_type
|| ') '
|| '-> still invalid!');
err_code := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 200);
INSERT INTO t_cmp_invalid_objects_log
VALUES (s_cpl_inv_objects_error_log.NEXTVAL,
cur_object.object_name,
cur_object.owner,
cur_object.object_type,
err_code || ' -ERROR- ' || err_msg);
COMMIT;
END;
END LOOP;
str_exec := 'select count(*) FROM dba_objects
WHERE status = ''INVALID''';
EXECUTE IMMEDIATE str_exec INTO total_inv;
DBMS_OUTPUT.put_line ('Total invalid number: ' || total_inv);
END;
/
DROP TABLE t_cmp_invalid_objects_log
/
CREATE TABLE t_cmp_invalid_objects_log
(
log_id INT PRIMARY KEY,
object_name VARCHAR2 (100),
object_schema VARCHAR2 (100),
object_type VARCHAR2 (100),
error VARCHAR2 (1000)
)
/
DROP SEQUENCE s_cpl_inv_objects_error_log
/
CREATE SEQUENCE s_cpl_inv_objects_error_log MINVALUE 0
START WITH 0
INCREMENT BY 1
CACHE 5;
Usage:
exec P_COMPILE_INVALID_OBJECTS();
Check error logs:
SQL> show error;
or
SQL> SELECT *
FROM ALL_ERRORS
WHERE OWNER = USER;