Oracle PL/SQL/System Packages/dbms utility

Assign dbms_utility.get_time to integer variable

  
SQL>
SQL> create or replace procedure num_test_as_integer is
  2   x integer;
  3   t number := dbms_utility.get_time;
  4   begin
  5       for i in 1 .. 1000 loop
  6           x := i;
  7       end loop;
  8       dbms_output.put_line((dbms_utility.get_time-t)||"cs");
  9   end;
 10  /
Procedure created.
SQL>

Call dbms_utility.get_time twice to time a statement in PL SQL

   
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
  2          l_start 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-l_start || " hsecs" );
 10  end;
 11  /
21 hsecs
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL> --

DBMS_UTILITY.analyze_schema

   
SQL>
SQL> CREATE OR REPLACE PROCEDURE analyze_oem
  2  IS
  3     CURSOR curs_get_hour IS SELECT TO_CHAR (SYSDATE, "HH24") FROM dual;
  4     v_current_hour NUMBER (2);
  5     v_method_opt VARCHAR2 (15);
  6  BEGIN
  7     OPEN curs_get_hour;
  8     FETCH curs_get_hour INTO v_current_hour;
  9     CLOSE curs_get_hour;
 10
 11     IF v_current_hour IN (1, 2)
 12     THEN
 13        v_method_opt := NULL;
 14     ELSE
 15        v_method_opt := "FOR ALL INDEXES";
 16     END IF;
 17
 18     DBMS_UTILITY.analyze_schema (
 19        schema => "OEM",
 20        method => "COMPUTE",
 21        method_opt => v_method_opt
 22     );
 23  END analyze_oem;
 24  /
Procedure created.

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_parameter_value

   
SQL>
SQL> declare
  2    l_intval number;
  3    l_strval varchar2(2000);
  4    l_type   number;
  5    begin
  6       l_type := dbms_utility.get_parameter_value("user_dump_dest", l_intval, l_strval);
  7       dbms_output.put_line(l_strval );
  8    end;
  9  /
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP
PL/SQL procedure successfully completed.
SQL>
SQL>

demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.

   
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    v_InitialString VARCHAR2(100) := "Alpha, Bravo, Charlie, Delta, Epsilon, Frank, George";
  3    v_Table DBMS_UTILITY.UNCL_ARRAY;
  4    v_FinalString VARCHAR2(100);
  5    v_TabLen BINARY_INTEGER;
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE("Initial string: " || v_InitialString);
  8    DBMS_UTILITY.ruMA_TO_TABLE(v_InitialString, v_TabLen, v_Table);
  9    DBMS_OUTPUT.PUT_LINE("Table (length = " || v_TabLen || "):");
 10    FOR v_Count IN 1..v_Table.COUNT LOOP
 11      DBMS_OUTPUT.PUT_LINE(v_Table(v_Count));
 12    END LOOP;
 13    DBMS_UTILITY.TABLE_TO_COMMA(v_Table, v_TabLen, v_FinalString);
 14    DBMS_OUTPUT.PUT_LINE("Final string (length = " || v_TabLen || "):");
 15    DBMS_OUTPUT.PUT_LINE(v_FinalString);
 16  END;
 17  /
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
PL/SQL procedure successfully completed.
SQL>

Performace difference between simple parameter and collection parameter

   
SQL> create or replace procedure SIMPLE_PARM(p number) is
  2   x number;
  3   begin
  4       null;
  5   end;
  6  /
Procedure created.
SQL>
SQL> create or replace type rec is object
  2   ( a number,
  3   b number,
  4   c varchar2(30));
  5  /

SQL>
SQL> create or replace type rec_list is table of rec;
  2  /
Type created.
SQL>
SQL> create or replace procedure big_parm(p Rec_list) is
  2   x number;
  3   begin
  4       null;
  5   end;
  6  /
Procedure created.
SQL>
SQL>
SQL>
SQL> declare
  2   x rec_list := rec_list();
  3   t1 number;
  4   t2 number;
  5   begin
  6       x.extend(50000);
  7       for i in 1 .. 50000 loop
  8          x(i) := rec(i,i,rpad(i,30));
  9       end loop;
 10       t1 := dbms_utility.get_time;
 11       for i in 1 .. 500000 loop
 12          simple_parm(i);
 13       end loop;
 14       t2 := dbms_utility.get_time;
 15       dbms_output.put_line("Simple: "||(t2-t1));
 16       for i in 1 .. 500000 loop
 17          big_parm(x);
 18       end loop;
 19       t1 := dbms_utility.get_time;
 20       dbms_output.put_line("Collection:"||(t1-t2));
 21   end;
 22  /
Simple: 19
Collection:15
PL/SQL procedure successfully completed.

round dbms_utility.get_time

   
SQL>
SQL> declare l_tmp char(1);
  2  l_start number := dbms_utility.get_time;
  3  begin
  4  select * into l_tmp from dual;
  5  dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 )||" seconds" );
  6  end;
  7  /
0 seconds
PL/SQL procedure successfully completed.
SQL>
SQL>

This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.

   
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE Tokenize(name IN VARCHAR2) AS
  2    v_A VARCHAR2(256);
  3    v_B VARCHAR2(256);
  4    v_C VARCHAR2(256);
  5    v_DBLink VARCHAR2(256);
  6    v_NextPos BINARY_INTEGER;
  7  BEGIN
  8    DBMS_UTILITY.NAME_TOKENIZE(name, v_A, v_B, v_C, v_DBLink,
  9                               v_NextPos);
 10    DBMS_OUTPUT.PUT_LINE("a: " || v_A);
 11    DBMS_OUTPUT.PUT_LINE("b: " || v_B);
 12    DBMS_OUTPUT.PUT_LINE("c: " || v_C);
 13    DBMS_OUTPUT.PUT_LINE("db link: " || v_DBLink);
 14    DBMS_OUTPUT.PUT_LINE("next pos: " || v_NextPos);
 15  END Tokenize;
 16  /
Procedure created.
SQL>
SQL> exec Tokenize("example.lecturer");
a: EXAMPLE
b: LECTURER
c:
db link:
next pos: 16
PL/SQL procedure successfully completed.

time to commit

   
SQL>
SQL> set echo on
SQL>
SQL> create table t
  2  as
  3  select * from all_objects;
Table created.
SQL>
SQL> insert into t select * from t;
12210 rows created.
SQL> insert into t select * from t;
24420 rows created.
SQL> insert into t select * from t where rownum < 12000;
11999 rows created.
SQL> insert into t select * from t;
60839 rows created.
SQL> insert into t select * from t;
121678 rows created.
SQL> insert into t select * from t;
243356 rows created.
SQL> insert into t select * from t;
486712 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace procedure do_commit( p_rows in number )
  2  as
  3      l_start        number;
  4      l_after_redo   number;
  5      l_before_redo  number;
  6  begin
  7      l_start := dbms_utility.get_time;
  8      insert into t select * from t where rownum < p_rows;
  9      dbms_output.put_line( sql%rowcount || " rows created" );
 10      dbms_output.put_line ( "Time to INSERT: " ||to_char( round( (dbms_utility.get_time-l_start)/100, 5 ), "999.99") || " seconds" );
 11
 12      l_start := dbms_utility.get_time;
 13      commit;
 14      dbms_output.put_line( "Time to COMMIT: " ||to_char( round( (dbms_utility.get_time-l_start)/100, 5 ), "999.99") || " seconds" );
 15
 16      dbms_output.put_line( "Generated " ||  to_char(l_after_redo-l_before_redo,"999,999,999,999") || " bytes of redo" );
 17      dbms_output.new_line;
 18  end;
 19  /
Procedure created.
SQL> show error
No errors.
SQL>
SQL> set serveroutput on format wrapped
SQL> begin
  2      for i in 1 .. 5
  3      loop
  4          do_commit( power(10,i) );
  5      end loop;
  6  end;
  7  /
9 rows created
Time to INSERT:     .06 seconds
Time to COMMIT:     .00 seconds
Generated  bytes of redo
99 rows created
Time to INSERT:     .00 seconds
Time to COMMIT:     .00 seconds
Generated  bytes of redo
999 rows created
Time to INSERT:     .02 seconds
Time to COMMIT:     .00 seconds
Generated  bytes of redo
9999 rows created
Time to INSERT:     .31 seconds
Time to COMMIT:     .00 seconds
Generated  bytes of redo
99999 rows created
Time to INSERT:     .80 seconds
Time to COMMIT:     .00 seconds
Generated  bytes of redo

PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --

Timing Package function call

   
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE CopyFast AS
  2    TYPE StudentArray IS
  3      TABLE OF lecturer%ROWTYPE;
  4
  5    PROCEDURE Passlecturer1(p_Parameter IN StudentArray);
  6    PROCEDURE Passlecturer2(p_Parameter IN OUT StudentArray);
  7    PROCEDURE Passlecturer3(p_Parameter IN OUT NOCOPY StudentArray);
  8
  9    PROCEDURE Go;
 10  END CopyFast;
 11  /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY CopyFast AS
  2    PROCEDURE Passlecturer1(p_Parameter IN StudentArray) IS
  3    BEGIN
  4      NULL;
  5    END Passlecturer1;
  6
  7    PROCEDURE Passlecturer2(p_Parameter IN OUT StudentArray) IS
  8    BEGIN
  9      NULL;
 10    END Passlecturer2;
 11
 12    PROCEDURE Passlecturer3(p_Parameter IN OUT NOCOPY StudentArray) IS
 13    BEGIN
 14      NULL;
 15    END Passlecturer3;
 16
 17    PROCEDURE Go IS
 18      myLecturerArray StudentArray := StudentArray(NULL);
 19      myLecturerRec lecturer%ROWTYPE;
 20      v_Time1 NUMBER;
 21      v_Time2 NUMBER;
 22      v_Time3 NUMBER;
 23      v_Time4 NUMBER;
 24    BEGIN
 25      SELECT *
 26        INTO myLecturerArray(1)
 27        FROM lecturer
 28        WHERE ID = 10007;
 29      myLecturerArray.EXTEND(50000, 1);
 30
 31      v_Time1 := DBMS_UTILITY.GET_TIME;
 32      Passlecturer1(myLecturerArray);
 33      v_Time2 := DBMS_UTILITY.GET_TIME;
 34      Passlecturer2(myLecturerArray);
 35      v_Time3 := DBMS_UTILITY.GET_TIME;
 36      Passlecturer3(myLecturerArray);
 37      v_Time4 := DBMS_UTILITY.GET_TIME;
 38
 39      DBMS_OUTPUT.PUT_LINE("Time to pass IN: " || TO_CHAR((v_Time2 - v_Time1) / 100));
 40      DBMS_OUTPUT.PUT_LINE("Time to pass IN OUT: " || TO_CHAR((v_Time3 -   v_Time2) / 100));
 41      DBMS_OUTPUT.PUT_LINE("Time to pass IN OUT NOCOPY: " || TO_CHAR((v_Time4 - v_Time3) / 100));
 42    END Go;
 43  END CopyFast;
 44  /
Package body created.
SQL> show errors
No errors.
SQL>
SQL> BEGIN
  2    CopyFast.Go;
  3  END;
  4  /
Time to pass IN: 0
Time to pass IN OUT: .19
Time to pass IN OUT NOCOPY: 0
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>

Timing Per Thousand Records Processed (in secs)

   
SQL>
SQL>
SQL> DECLARE
  2     lv_counter_num        PLS_INTEGER := 0;
  3     lv_timer_start_num    NUMBER;
  4     lv_timer_previous_num NUMBER;
  5     lv_timer_current_num  NUMBER;
  6  BEGIN
  7     lv_timer_start_num    := DBMS_UTILITY.GET_TIME;
  8     lv_timer_previous_num := lv_timer_start_num;
  9     LOOP
 10        lv_counter_num := lv_counter_num + 1;
 11        IF MOD(lv_counter_num, 1000) = 0 THEN
 12           lv_timer_current_num := DBMS_UTILITY.GET_TIME;
 13           DBMS_OUTPUT.PUT_LINE("Time Elapsed-Total: " ||
 14              (lv_timer_current_num - lv_timer_start_num)/100 ||
 15              CHR(9) || " This Set: " ||
 16              (lv_timer_current_num - lv_timer_previous_num)/100 ||
 17              CHR(9) || " Records Processed: " ||
 18              lv_counter_num);
 19           lv_timer_previous_num := lv_timer_current_num;
 20         END IF;
 21         EXIT WHEN lv_counter_num = 5540;
 22     END LOOP;
 23     lv_timer_current_num := DBMS_UTILITY.GET_TIME;
 24     DBMS_OUTPUT.PUT_LINE("Time Elapsed-Total: ");
 25     DBMS_OUTPUT.PUT_LINE(lv_timer_current_num - lv_timer_start_num );
 26     DBMS_OUTPUT.PUT_LINE(" This Set: " );
 27     DBMS_OUTPUT.PUT_LINE(lv_timer_current_num - lv_timer_previous_num);
 28     DBMS_OUTPUT.PUT_LINE(" Records Processed: " ||lv_counter_num);
 29  END;
 30  /
Time Elapsed-Total: 0    This Set: 0     Records Processed: 1000
Time Elapsed-Total: 0    This Set: 0     Records Processed: 2000
Time Elapsed-Total: 0    This Set: 0     Records Processed: 3000
Time Elapsed-Total: .01  This Set: .01   Records Processed: 4000
Time Elapsed-Total: .01  This Set: 0     Records Processed: 5000
Time Elapsed-Total:
1
 This Set:
0
 Records Processed: 5540
PL/SQL procedure successfully completed.
SQL>

use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.

   
SQL>
SQL>
SQL> CREATE TABLE error_log (
  2    timestamp     DATE,
  3    username      VARCHAR2(30),
  4    instance      NUMBER,
  5    database_name VARCHAR2(50),
  6    error_stack   VARCHAR2(2000)
  7    );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER LogErrors
  2    AFTER SERVERERROR ON DATABASE
  3  BEGIN
  4    INSERT INTO error_log
  5      VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
  6              DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
  7  END LogErrors;
  8  /
Trigger created.
SQL>
SQL> SELECT * FROM non_existent_table;
SELECT * FROM non_existent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> BEGIN
  2    INSERT INTO non_existent_table VALUES ("Hello!");
  3  END;
  4  /
  INSERT INTO non_existent_table VALUES ("Hello!");
              *
ERROR at line 2:
ORA-06550: line 2, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored

SQL> COLUMN error_stack FORMAT a61 WRAPPED
SQL> SET LINE 80
SQL>
SQL> SELECT * FROM error_log;
TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 sqle                                  1
XE
ORA-00942: table or view does not exist
18-JUN-08 sqle                                  1
XE
ORA-06550: line 2, column 15:
TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored

SQL>
SQL> DROP TABLE error_log;
Table dropped.
SQL>
SQL> drop trigger LOGERRORS;
Trigger dropped.
SQL>

Use dbms_utility.get_time to do performace check

  
SQL>
SQL>
SQL>
SQL> declare
  2   type charListType is table of number index by varchar2(100);
  3   vc charListType;
  4   type numberListType is table of number index by binary_integer;
  5   n numberListType;
  6   t number;
  7   begin
  8       t := dbms_utility.get_time;
  9       for i in 1 .. 1000 loop
 10           n(i) := i;
 11       end loop;
 12       dbms_output.put_line("Index by Number : "|| (dbms_utility.get_time-t));
 13       t := dbms_utility.get_time;
 14       for i in 1 .. 1000 loop
 15           vc(i) := i;
 16       end loop;
 17       dbms_output.put_line("Index by Varchar2: "|| (dbms_utility.get_time-t));
 18   end;
 19  /
Index by Number : 0
Index by Varchar2: 0
PL/SQL procedure successfully completed.
SQL>

Use dbms_utility to time

   
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      l_start number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for "select object_name from all_objects where object_id = " || i;
 10          fetch l_rc into l_dummy;
 11          close l_rc;
 12      end loop;
 13      dbms_output.put_line( round( (dbms_utility.get_time-l_start)/100, 2 ) || " seconds..." );
 14  end;
 15  /
37.76 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:38.10
SQL> set timing off
SQL>
SQL> --