Handy Package DBMS_UTILITY
Posted by Sriram Sanka on December 10, 2010
Really Its great handy package for Oracle People!
We can use this in so many different scenarios.
SQL> DECLARE
2 parnam VARCHAR2(256);
3 intval BINARY_INTEGER;
4 strval VARCHAR2(256);
5 partyp BINARY_INTEGER;
6 BEGIN
7 partyp := dbms_utility.get_parameter_value('max_dump_file_size',
8 intval, strval);
9 dbms_output.put('parameter value is: ');
10 IF partyp = 1 THEN
11 dbms_output.put_line(strval);
12 ELSE
13 dbms_output.put_line(intval);
14 END IF;
15 IF partyp = 1 THEN
16 dbms_output.put('parameter value length is: ');
17 dbms_output.put_line(intval);
18 END IF;
19 dbms_output.put('parameter type is: ');
20 IF partyp = 1 THEN
21 dbms_output.put_line('string');
22 ELSE
23 dbms_output.put_line('integer');
24 END IF;
25 END;
26 /
parameter value is: UNLIMITED
parameter value length is: 9
parameter type is: string
PL/SQL procedure successfully completed.
SQL> declare
2 ver varchar2(100);
3 com varchar2(100);
4 begin
5 dbms_utility.db_version(ver,com);
6 dbms_output.put_line(ver||'-'||com);
7 end;
8 /
10.2.0.1.0-10.2.0.1.0
PL/SQL procedure successfully completed.
SQL> declare
2 id number;
3 str varchar2(40);
4 begin
5 id:=dbms_utility.get_parameter_value('compatible',id,str);
6 dbms_output.put_line(str);
7 end;
8 /
10.2.0.1.0
PL/SQL procedure successfully completed.
SQL> create or replace procedure sriram
2 as
3 begin
4 NULL;
5 end;
6 /
Procedure created.
SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';
OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 VALID SRIRAM
SQL> exec dbms_utility.invalidate(52565);
PL/SQL procedure successfully completed.
SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';
OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 INVALID SRIRAM
SQL> exec dbms_utility.validate(52565);
PL/SQL procedure successfully completed.
SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';
OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 VALID SRIRAM
SQL>
We can use This package in different situations like to compile schema objects analtze database etc…..
My Experiences with Databases & More 
Leave a Reply