My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • December 2010
    M T W T F S S
    « Oct   May »
     12345
    6789101112
    13141516171819
    20212223242526
    2728293031  
  • Blogs I Follow

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…..

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.

 
Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBA's become bulletproof!

%d bloggers like this: