Oracle knowledge: June 2009

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;

/

Oracle knowledge

Thursday, June 11, 2009

Find the Binary value of a number (<1024)

Sunday, June 07, 2009

recompile function by - Solomon Yakobson

Usefull Links

Labels