CREATE OR REPLACE FUNCTION recompile(o_owner IN VARCHAR2 := '%',
o_name IN VARCHAR2 := '%',
o_type IN VARCHAR2 := '%',
o_status IN VARCHAR2 := 'INVALID',
display IN BOOLEAN := TRUE)
RETURN NUMBER AUTHID CURRENT_USER IS
-- _____________________________________________________________________
--
--
Recompile Utility
--
____________________________________________________________________
--
-- FILE: recompile.sql
-- TITLE: Recompile Utility
-- TYPE: ORACLE PL/SQL Stored Function
-- VERSION: 2.0
-- CREATED: August 3, 1998
-- AUTHOR: Solomon Yakobson
-- WARNING:
-- SCOPE: Recompile Utility can be used for Oracle 7.3 and above object compilation.
--
-- MODIFICATION
-- HISTORY: September 9, 1998 - fixed obj_cursor to include objects
-- with no dependencies.
--
-- May 12, 1999 - fix for DBMS_SQL behavior change in
-- Oracle 8 (most likely it is a bug).
-- If object recompilation has errors,
-- ORACLE 8 DBMS_SQL raises exception:
-- ORA-24333: success with compilation
-- error, followed by host environment
-- (e.g. SQL*Plus) internal error and
-- Unsafe to proceed message.
--
-- May 12, 1999 - added COMPILE_ERRORS return code.
--
-- May 12, 1999 - added TYPE and TYPE BODY objects.
--
-- Jan 10, 2006 - upgraded by Steven Feuerstein
--
-- DESCRIPTION: Recompile Utility is designed to compile the following
-- types of objects:
--
-- PROCEDURE (ORACLE 7 & 8),
-- FUNCTION (ORACLE 7 & 8),
-- PACKAGE - specification and body (ORACLE 7 & 8),
-- PACKAGE BODY - body only (ORACLE 7 & 8),
-- TRIGGER (ORACLE 7 & 8),
-- VIEW (ORACLE 7 & 8),
-- TYPE - specification only (ORACLE 8),
-- TYPE BODY - body only (ORACLE 8).
--
-- Objects are recompiled based on object dependencies and
-- therefore compiling all requested objects in one path.
-- Recompile Utility skips every object which is either of
-- unsupported object type or depends on INVALID object(s)
-- outside of current request (which means we know upfront
-- compilation will fail anyway). If object recompilation
-- is not successful, Recompile Utility continues with the
-- next object. Recompile Utility has five parameters:
--
-- o_owner - IN mode parameter is a VARCHAR2 defining
-- owner of to be recompiled objects. It
-- accepts operator LIKE widcards. Backslash
-- (\) is used for escaping wildcards. If
-- omitted, parameter defaults to USER.
-- o_name - IN mode parameter is a VARCHAR2 defining
-- names of to be recompiled objects. It
-- accepts operator LIKE widcards. Backslash
-- (\) is used for escaping wildcards. If
-- omitted, it defaults to '%' - any name.
-- o_type - IN mode parameter is a VARCHAR2 defining
-- types of to be recompiled objects. It
-- accepts operator LIKE widcards. Backslash
-- (\) is used for escaping wildcards. If
-- omitted, it defaults to '%' - any type.
-- o_status - IN mode parameter is a VARCHAR2 defining
-- status of to be recompiled objects. It
-- accepts operator LIKE widcards. Backslash
-- (\) is used for escaping wildcards. If
-- omitted, it defaults to 'INVALID'.
-- display - IN mode parameter is a BOOLEAN defining
-- whether object recompile status is written
-- to DBMS_OUTPUT buffer. If omitted, it
-- defaults to TRUE.
--
-- Recompile Utility returns the following values or their
-- combinations:
--
-- 0 - Success. All requested objects are recompiled and
-- are VALID.
-- 1 - INVALID_TYPE. At least one of to be recompiled
-- objects is not of supported object type.
-- 2 - INVALID_PARENT. At least one of to be recompiled
-- objects depends on an invalid object outside of
-- current request.
-- 4 - COMPILE_ERRORS. At least one of to be recompiled
-- objects was compiled with errors and is INVALID.
--
-- If parameter display is set to TRUE, Recompile Utility
-- writes the following information to DBMS_OUTPUT buffer:
--
-- RECOMPILING OBJECTS
--
-- Object Owner is o_owner
-- Object Name is o_name
-- Object Type is o_type
-- Object Status is o_status
--
-- TTT OOO.NNN is recompiled. Object status is SSS.
-- TTT OOO.NNN references invalid object(s) outside of
-- this request.
-- OOO.NNN is TTT and can not be recompiled.
--
-- where o_owner is parameter o_owner value, o_name is
-- parameter o_name value, o_type is parameter o_type
-- value and o_status is is parameter o_status value. TTT
-- is object type, OOO is object owner, NNN is object name
-- and SSS is object status after compilation.
--
-- NOTES: If parameter display is set to TRUE, you MUST ensure
-- DBMS_OUTPUT buffer is large enough for produced output.
-- Otherwise Recompile Utility will not recompile all the
-- objects. If used in SQL*Plus, issue:
--
-- SET SERVEROUTPUT ON SIZE xxx FORMAT WRAPPED
--
-- FORMAT WRAPPED is needed for text alignment.
-- ______________________________________________________________________
--
-- Exceptions
successwithcompilationerror EXCEPTION;
PRAGMA EXCEPTION_INIT(successwithcompilationerror, -24344);
-- Return Codes
invalid_type CONSTANT INTEGER := 1;
invalid_parent CONSTANT INTEGER := 2;
compile_errors CONSTANT INTEGER := 4;
cnt NUMBER;
dyncur INTEGER;
type_status INTEGER := 0;
parent_status INTEGER := 0;
recompile_status INTEGER := 0;
object_status VARCHAR2(30);
CURSOR invalid_parent_cursor(oowner VARCHAR2, oname VARCHAR2, otype VARCHAR2, ostatus VARCHAR2, OID NUMBER) IS
SELECT /*+ RULE */
o.object_id
FROM public_dependency d, all_objects o
WHERE d.object_id = OID
AND o.object_id = d.referenced_object_id
AND o.status != 'VALID'
MINUS
SELECT /*+ RULE */
object_id
FROM all_objects
WHERE owner LIKE upper(oowner)
AND object_name LIKE upper(oname)
AND object_type LIKE upper(otype)
AND status LIKE upper(ostatus);
CURSOR recompile_cursor(OID NUMBER) IS
SELECT /*+ RULE */
'ALTER '
decode(object_type,
'PACKAGE BODY',
'PACKAGE',
'TYPE BODY',
'TYPE',
object_type)
' '
owner
'.'
object_name
' COMPILE '
decode(object_type,
'PACKAGE BODY',
' BODY',
'TYPE BODY',
'BODY',
'TYPE',
'SPECIFICATION',
'')
decode(object_type, 'VIEW', '', ' REUSE SETTINGS') stmt,
object_type,
owner,
object_name
FROM all_objects
WHERE object_id = OID;
recompile_record recompile_cursor%ROWTYPE;
CURSOR obj_cursor(oowner VARCHAR2, oname VARCHAR2, otype VARCHAR2, ostatus VARCHAR2) IS
SELECT /*+ RULE */
MAX(LEVEL) dlevel, object_id
FROM sys.public_dependency
START WITH object_id IN
(SELECT object_id
FROM all_objects
WHERE owner LIKE upper(oowner)
AND object_name LIKE upper(oname)
AND object_type LIKE upper(otype)
AND status LIKE upper(ostatus))
CONNECT BY NOCYCLE object_id = PRIOR referenced_object_id
GROUP BY object_id
HAVING MIN(LEVEL) = 1
UNION ALL
SELECT 1 dlevel, object_id
FROM all_objects o
WHERE owner LIKE upper(oowner)
AND object_name LIKE upper(oname)
AND object_type LIKE upper(otype)
AND status LIKE upper(ostatus)
AND NOT EXISTS (SELECT 1
FROM sys.public_dependency d
WHERE d.object_id = o.object_id)
ORDER BY 1 DESC;
TYPE integer_tt IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
l_dlevel integer_tt;
l_object_id integer_tt;
CURSOR status_cursor(OID NUMBER) IS
SELECT /*+ RULE */
status
FROM all_objects
WHERE object_id = OID;
BEGIN
-- Recompile requested objects based on their dependency levels.
IF display THEN
dbms_output.put_line(chr(0));
dbms_output.put_line(' RECOMPILING OBJECTS');
dbms_output.put_line(chr(0));
dbms_output.put_line(' Object Owner is '
o_owner);
dbms_output.put_line(' Object Name is '
o_name);
dbms_output.put_line(' Object Type is '
o_type);
dbms_output.put_line(' Object Status is '
o_status);
dbms_output.put_line(chr(0));
END IF;
dyncur := dbms_sql.open_cursor;
OPEN obj_cursor(o_owner, o_name, o_type, o_status);
FETCH obj_cursor BULK COLLECT
INTO l_dlevel, l_object_id;
FOR indx IN 1 .. l_dlevel.COUNT LOOP
OPEN recompile_cursor(l_object_id(indx));
FETCH recompile_cursor
INTO recompile_record;
CLOSE recompile_cursor;
-- We can recompile only Functions, Packages, Package Bodies,
-- Procedures, Triggers, Views, Types and Type Bodies.
IF recompile_record.object_type IN
('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER',
'VIEW', 'TYPE', 'TYPE BODY') THEN
-- There is no sense to recompile an object that depends on
-- invalid objects outside of the current recompile request.
OPEN invalid_parent_cursor(o_owner,
o_name,
o_type,
o_status,
l_object_id(indx));
FETCH invalid_parent_cursor
INTO cnt;
IF invalid_parent_cursor%NOTFOUND THEN
-- Recompile object.
BEGIN
dbms_sql.parse(dyncur, recompile_record.stmt, dbms_sql.native);
EXCEPTION
WHEN successwithcompilationerror THEN
NULL;
END;
OPEN status_cursor(l_object_id(indx));
FETCH status_cursor
INTO object_status;
CLOSE status_cursor;
IF display THEN
dbms_output.put_line(recompile_record.object_type
' '
recompile_record.owner
'.'
recompile_record.object_name
' is recompiled. Object status is '
object_status
'.');
END IF;
IF object_status <> 'VALID' THEN
recompile_status := compile_errors;
END IF;
ELSE
IF display THEN
dbms_output.put_line(recompile_record.object_type
' '
recompile_record.owner
'.'
recompile_record.object_name
' references invalid object(s)'
' outside of this request.');
END IF;
parent_status := invalid_parent;
END IF;
CLOSE invalid_parent_cursor;
ELSE
IF display THEN
dbms_output.put_line(recompile_record.owner
'.'
recompile_record.object_name
' is a '
recompile_record.object_type
' and can not be recompiled.');
END IF;
type_status := invalid_type;
END IF;
END LOOP;
dbms_sql.close_cursor(dyncur);
RETURN type_status + parent_status + recompile_status;
EXCEPTION
WHEN OTHERS THEN
IF obj_cursor%ISOPEN THEN
CLOSE obj_cursor;
END IF;
IF recompile_cursor%ISOPEN THEN
CLOSE recompile_cursor;
END IF;
IF invalid_parent_cursor%ISOPEN THEN
CLOSE invalid_parent_cursor;
END IF;
IF status_cursor%ISOPEN THEN
CLOSE status_cursor;
END IF;
IF dbms_sql.is_open(dyncur) THEN
dbms_sql.close_cursor(dyncur);
END IF;
RAISE;
END;
/
No comments:
Post a Comment