My Experiments with Oracle

By Sriram Sanka -The AUAA

Handy Package DBMS_UTILITY

Posted by sriram 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…..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: