The
DBMS_UTILITY package is a collection of miscellaneous procedures. It is where
many, standalone procedures are placed. The DBMS_UTILITY package is installed
in the database by default, and has EXECUTE granted to PUBLIC.
DBMS_UTILITY Package constructs:
DBMS_UTILITY.COMPILE_SCHEMA:
To
compile the schema
exec dbms_utility.compile_schema
('MAHENDRA');
Note:
1.Schema
name is case sensitive above.
2.For
statistics collection, analyzing schema objects use dbms_stats in place of
dbms_utility.analyze_schema, dbms_utility.analyze_database
3.Format_error_stack,
format_error_backtrace, format_call_stack are helpful in getting the proper
error messages
4. Get_time,
get_cpu_time returns time in milli seconds
Example:
declare
v_time number;
v_cpu_time number;
begin
select dbms_utility.get_time, dbms_utility.get_cpu_time into v_time, v_cpu_time from dual;
dbms_output.put_line ( 'Before v_time :
v_cpu_time ->' ||
v_time ||' : ' || v_cpu_time );
for i in 1..100 loop
dbms_lock.sleep (1);
end loop;
select dbms_utility.get_time, dbms_utility.get_cpu_time into v_time, v_cpu_time from dual;
dbms_output.put_line ( 'After v_time :
v_cpu_time ->' ||
v_time ||' : ' || v_cpu_time );
exception
when others then
dbms_output.put_line (sqlerrm ||'-' || dbms_utility.format_error_stack );
end;
output:
Before v_time :
v_cpu_time ->225371913 : 275
After
v_time : v_cpu_time ->225381915 : 277
Get_parameter_value
declare
v_str1 varchar2(1000);
v_str2 varchar2(1000);
v_suc int;
begin
v_suc := dbms_utility.get_parameter_value ('utl_file_dir', v_str1,v_str2) ;
dbms_output.put_line ('v_str1 -
v_str2 ->' ||v_str1||'-'|| v_str2 );
end;
v_str1
- v_str2 ->33-/u01/app/tst/oms/200/inv/data/usa
comma_to_table
& table_to_comma
declare
v_initialstring varchar2 (100) := 'Alpha, Bravo,
Charlie, Delta, Epsilon, Frank, George';
v_table dbms_utility.uncl_array;
v_finalstring varchar2 (100);
v_tablen binary_integer;
begin
dbms_output.put_line ('Initial string: ' || v_initialstring);
dbms_utility.comma_to_table (v_initialstring, v_tablen, v_table);
dbms_output.put_line ('Table (length = ' || v_tablen || '):');
for v_count in 1 .. v_table.count
loop
dbms_output.put_line (v_table (v_count));
end loop;
dbms_utility.table_to_comma (v_table, v_tablen, v_finalstring);
dbms_output.put_line ('Final string
(length = ' ||
v_tablen || '):');
dbms_output.put_line (v_finalstring);
end;
output:
Initial string:
Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George
Table (length = 7):
Alpha
Bravo
Charlie
Delta
Epsilon
Frank
George
Final string (length
= 7):
Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George
Name_tokenize
Calls the parser to parse the given name as "a [. b [. c ]][@
dblink ]". It strips double quotes, or converts to uppercase if there are
no quotes. It ignores comments of all sorts, and does no semantic analysis.
Missing values are left as NULL.
DECLARE
a VARCHAR2(30);
b VARCHAR2(30);
c VARCHAR2(30);
d VARCHAR2(30);
i BINARY_INTEGER;
BEGIN
dbms_utility.name_tokenize('UWCLASS.PERSON.SSN', a, b, c, d, i);
dbms_output.put_line('Owner: ' || a);
dbms_output.put_line('Table: ' || b);
dbms_output.put_line('Column: ' || c);
dbms_output.put_line('Link: ' || d);
END;
Output:
Owner: UWCLASS
Table: PERSON
Column: SSN
Link:
DB_VERSION
and PORT_STRING
declare
l_version varchar2(255);
l_compatibility varchar2(255);
begin
dbms_utility.db_version( l_version, l_compatibility );
dbms_output.put_line( l_version );
dbms_output.put_line( l_compatibility );
end;
Output:
10.2.0.4.0
10.2.0.4
format_call_stack
test:
create or replace procedure p1
as
begin
raise program_error;
end;
/
create or replace procedure p2
as
begin
p1;
end;
/
create or replace procedure p3
as
begin
p2;
end;
/
exec p3
create or replace procedure p3
as
begin
p2;
exception
when others then
dbms_output.put_line( dbms_utility.format_call_stack
);
end;
/
exec p3
Output:
sql> exec p3;
----- PL/SQL Call
Stack -----
object line
object
handle number
name
3988fa058 7
procedure OMSOWN.P3
39b46bcc8 1
anonymous
block
No comments:
Post a Comment