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
<source lang="sql">
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></source>
Call dbms_utility.analyze_schema analyze schema
<source lang="sql">
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></source>
Call dbms_utility.db_version to get the version and its compatible version
<source lang="sql">
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</source>
Call dbms_utility.get_parameter_value to get the value of utl_file_dir
<source lang="sql">
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></source>
Call dbms_utility.get_time twice
<source lang="sql">
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.</source>
Call dbms_utility.get_time twice to time the insert statement
<source lang="sql">
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.</source>
DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK
<source lang="sql">
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></source>
dbms_utility.format_error_stack
<source lang="sql">
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></source>
dbms_utility.get_hash_value
<source lang="sql">
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></source>
Timing log
<source lang="sql">
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.</source>
Use dbms_utility.comma_to_table, dbms_utility.table_to_comma to parse comma delimited value
<source lang="sql">
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></source>
Use dbms_utility.name_resolve to find a package
<source lang="sql">
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.</source>
Use dbms_utility.name_tokenize to split a name
<source lang="sql">
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></source>