Oracle PL/SQL Tutorial/System Packages/dbms utility
Содержание
- 1 Assign value from dbms_utility.get_time to a number variable
- 2 Call dbms_utility.analyze_schema analyze schema
- 3 Call dbms_utility.db_version to get the version and its compatible version
- 4 Call dbms_utility.get_parameter_value to get the value of utl_file_dir
- 5 Call dbms_utility.get_time twice
- 6 Call dbms_utility.get_time twice to time the insert statement
- 7 DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK
- 8 dbms_utility.format_error_stack
- 9 dbms_utility.get_hash_value
- 10 Timing log
- 11 Use dbms_utility.comma_to_table, dbms_utility.table_to_comma to parse comma delimited value
- 12 Use dbms_utility.name_resolve to find a package
- 13 Use dbms_utility.name_tokenize to split a name
Assign value from dbms_utility.get_time to a number variable
SQL>
SQL> set echo on
SQL>
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 myStart number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 1000
5 loop
6 insert into t values ( 1 );
7 end loop;
8 commit;
9 dbms_output.put_line( dbms_utility.get_time-myStart || " hsecs" );
10 end;
11 /
19 hsecs
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
Call dbms_utility.analyze_schema analyze schema
SQL>
SQL>
SQL> create table t ( x int primary key, y date )
2 organization index
3 OVERFLOW TABLESPACE TOOLS
4 /
SQL>
SQL> execute dbms_utility.analyze_schema("SCOTT","COMPUTE")
SQL>
SQL> select table_name, num_rows, last_analyzed from user_tables where table_name = "T";
no rows selected
SQL>
SQL> drop table t;
SQL>
Call dbms_utility.db_version to get the version and its compatible version
SQL>
SQL> set echo on
SQL>
SQL> declare
2 l_version varchar2(255);
3 l_compatibility varchar2(255);
4 begin
5 dbms_utility.db_version( l_version, l_compatibility );
6 dbms_output.put_line( l_version );
7 dbms_output.put_line( l_compatibility );
8 end;
9 /
10.2.0.1.0
10.2.0.1.0
PL/SQL procedure successfully completed.
SQL>
SQL> select dbms_utility.port_string from dual;
PORT_STRING
----------------------------------------------------------------------------------------------------
IBMPC/WIN_NT-8.1.0
Call dbms_utility.get_parameter_value to get the value of utl_file_dir
SQL>
SQL> declare
2 intval number;
3 strval varchar2(512);
4 begin
5 if ( dbms_utility.get_parameter_value( "utl_file_dir",intval,strval ) = 0 )
6 then
7 dbms_output.put_line( "Value = " || intval );
8 else
9 dbms_output.put_line( "Value = " || strval );
10 end if;
11 end;
12 /
Value = 0
PL/SQL procedure successfully completed.
SQL>
Call dbms_utility.get_time twice
SQL>
SQL> set echo on
SQL> set serverout on
SQL>
SQL> declare
2 myStart number;
3 n number := 0;
4 begin
5
6 myStart := dbms_utility.get_time;
7
8 for x in 1 .. 100000
9 loop
10 n := n+1;
11 end loop;
12
13 dbms_output.put_line(round( (dbms_utility.get_time-myStart)/100, 2 ) ||" seconds..." );
14 end;
15 /
.01 seconds...
PL/SQL procedure successfully completed.
Call dbms_utility.get_time twice to time the insert statement
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> declare
2 myStart number default dbms_utility.get_time;
3 begin
4 for i in 1 .. 1000
5 loop
6 insert into t values ( 1 );
7 commit;
8 end loop;
9 dbms_output.put_line( dbms_utility.get_time-myStart || " hsecs" );
10 end;
11 /
23 hsecs
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE error_test1 AS
2 BEGIN
3 RAISE VALUE_ERROR;
4 END error_test1;
5 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE error_test2 AS
2 BEGIN
3 error_test1;
4 EXCEPTION
5 WHEN OTHERS THEN
6 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
7 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
8 END error_test2;
9 /
Procedure created.
SQL>
dbms_utility.format_error_stack
SQL> DECLARE
2 myException EXCEPTION;
3 FUNCTION innerFunction
4 RETURN BOOLEAN IS
5 retval BOOLEAN := FALSE;
6 BEGIN
7 RAISE myException;
8 RETURN retval;
9 END;
10 BEGIN
11 IF innerFunction THEN
12 dbms_output.put_line("No raised exception");
13 END IF;
14 EXCEPTION
15 WHEN others THEN
16 dbms_output.put_line("DBMS_UTILITY.FORMAT_ERROR_STACK");
17 dbms_output.put_line(dbms_utility.format_error_stack);
18
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
dbms_utility.get_hash_value
SQL>
SQL> begin
2 dbms_output.put_line ( dbms_utility.get_hash_value( to_char(1), 0, 1024 ) );
3 end;
4 /
827
PL/SQL procedure successfully completed.
SQL>
Timing log
SQL>
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE process_timing_log
2 (program_name VARCHAR2(30),
3 execution_date DATE,
4 records_processed NUMBER,
5 elapsed_time_sec NUMBER);
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE update_salary AS
2 CURSOR empCursor IS
3 SELECT employee_id, salary, ROWID
4 FROM employee;
5 lv_new_salary_num NUMBER;
6 lv_count_num PLS_INTEGER := 0;
7 lv_start_time_num PLS_INTEGER;
8 lv_total_time_num NUMBER;
9 BEGIN
10 lv_start_time_num := DBMS_UTILITY.GET_TIME;
11 FOR empCursor_rec IN empCursor LOOP
12 lv_count_num := lv_count_num + 1;
13 lv_new_salary_num := empCursor_rec.salary;
14 UPDATE employee
15 SET salary = lv_new_salary_num
16 WHERE rowid = empCursor_rec.ROWID;
17 END LOOP;
18 lv_total_time_num := (DBMS_UTILITY.GET_TIME - lv_start_time_num)/100;
19 INSERT INTO process_timing_log(program_name, execution_date, records_processed,elapsed_time_sec)
20 VALUES("UPDATE_SALARY", SYSDATE, lv_count_num, lv_total_time_num);
21 COMMIT;
22 END update_salary;
23 /
Procedure created.
SQL>
SQL>
SQL> SELECT program_name,
2 TO_CHAR(execution_date,"MM/DD/YYYY HH24:MI:SS") execution_time,
3 records_processed, elapsed_time_sec
4 FROM process_timing_log
5 ORDER BY 1,2;
no rows selected
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
SQL> drop table process_timing_log;
Table dropped.
Use dbms_utility.comma_to_table, dbms_utility.table_to_comma to parse comma delimited value
SQL>
SQL> set echo on
SQL>
SQL> declare
2 type vcArray is table of varchar2(4000);
3
4 l_names vcArray := vcArray( "emp,dept,bonus",
5 "a, b , c",
6 ""1", "6", "9"",
7 ""This is a long string","b",c");
8 l_tablen number;
9 l_tab dbms_utility.uncl_array;
10 begin
11 for i in 1 .. l_names.count
12 loop
13 dbms_output.put_line( " " || "[" || l_names(i) || "]" );
14 begin
15 dbms_utility.ruma_to_table( l_names(i),l_tablen, l_tab );
16
17 for j in 1..l_tablen
18 loop
19 dbms_output.put_line( "[" || l_tab(j) || "]" );
20 end loop;
21 l_names(i) := null;
22 dbms_utility.table_to_comma( l_tab,l_tablen, l_names(i) );
23 dbms_output.put_line( l_names(i) );
24 exception
25 when others then dbms_output.put_line( sqlerrm );
26 end;
27 end loop;
28 end;
29 /
[emp,dept,bonus]
[emp]
[dept]
[bonus]
emp,dept,bonus
[a, b , c]
[a]
[ b ]
[ c]
a, b , c
["1", "6", "9"]
["1"]
[ "6"]
[ "9"]
"1", "6", "9"
["This is a long string","b",c]
["This is a long string"]
["b"]
[c]
"This is a long string","b",c
PL/SQL procedure successfully completed.
SQL>
Use dbms_utility.name_resolve to find a package
SQL>
SQL> set echo on
SQL> set serverout on
SQL>
SQL> declare
2 type vcArray is table of varchar2(30);
3 l_types vcArray := vcArray( null, null, null, null, "synonym",null, "procedure", "function","package" );
4 l_schema varchar2(30);
5 l_part1 varchar2(30);
6 l_part2 varchar2(30);
7 l_dblink varchar2(30);
8 l_type number;
9 l_obj# number;
10 begin
11 dbms_utility.name_resolve( name => "DBMS_UTILITY",
12 context => 1,
13 schema => l_schema,
14 part1 => l_part1,
15 part2 => l_part2,
16 dblink => l_dblink,
17 part1_type => l_type,
18 object_number => l_obj# );
19 if l_obj# IS NULL
20 then
21 dbms_output.put_line("Object not found or not valid.");
22 else
23 dbms_output.put( l_schema || "." || nvl(l_part1,l_part2) );
24 if l_part2 is not null and l_part1 is not null
25 then
26 dbms_output.put( "." || l_part2 );
27 end if;
28 dbms_output.put_line( " is a " || l_types( l_type ) ||
29 " with object id " || l_obj# ||
30 " and dblink "" || l_dblink || """ );
31 end if;
32 end;
33 /
SYS.DBMS_UTILITY is a package with object id 4224 and dblink ""
PL/SQL procedure successfully completed.
Use dbms_utility.name_tokenize to split a name
SQL> set echo on
SQL>
SQL> declare
2 l_a varchar2(30);
3 l_b varchar2(30);
4 l_c varchar2(30);
5 l_dblink varchar2(30);
6 l_next number;
7
8 type vcArray is table of varchar2(255);
9 l_names vcArray := vcArray( "owner.pkg.proc@database_link",
10 "owner.tbl@database_link",
11 "tbl",
12 ""Owner".tbl",
13 "pkg.proc",
14 "owner.pkg.proc",
15 "proc",
16 "owner.pkg.proc@dblink with junk",
17 "123" );
18 begin
19 for i in 1 .. l_names.count
20 loop
21 begin
22 dbms_utility.name_tokenize(name => l_names(i),
23 a => l_a,
24 b => l_b,
25 c => l_c,
26 dblink => l_dblink,
27 nextpos=> l_next );
28
29 dbms_output.put_line( "name " || l_names(i) );
30 dbms_output.put_line( "A " || l_a );
31 dbms_output.put_line( "B " || l_b );
32 dbms_output.put_line( "C " || l_c );
33 dbms_output.put_line( "dblink " || l_dblink );
34 dbms_output.put_line( "next " || l_next || " " || length(l_names(i)));
35 exception
36 when others then
37 dbms_output.put_line( "name " || l_names(i) );
38 dbms_output.put_line( sqlerrm );
39 end;
40 end loop;
41 end;
42 /
name owner.pkg.proc@database_link
A OWNER
B PKG
C PROC
dblink DATABASE_LINK
next 28 28
name owner.tbl@database_link
A OWNER
B TBL
C
dblink DATABASE_LINK
next 23 23
name tbl
A TBL
B
C
dblink
next 3 3
name "Owner".tbl
A Owner
B TBL
C
dblink
next 11 11
name pkg.proc
A PKG
B PROC
C
dblink
next 8 8
name owner.pkg.proc
A OWNER
B PKG
C PROC
dblink
next 14 14
name proc
A PROC
B
C
dblink
next 4 4
name owner.pkg.proc@dblink with junk
A OWNER
B PKG
C PROC
dblink DBLINK
next 22 31
name 123
ORA-00931: missing identifier
PL/SQL procedure successfully completed.
SQL>