Oracle PL/SQL/System Packages/dbms utility

Assign dbms_utility.get_time to integer variable

   <source lang="sql">
 

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>

 </source>
   
  

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

   <source lang="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> --

 </source>
   
  

DBMS_UTILITY.analyze_schema

   <source lang="sql">
  

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.

 </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_parameter_value

   <source lang="sql">
  

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>

 </source>
   
  

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

   <source lang="sql">
  

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>

 </source>
   
  

Performace difference between simple parameter and collection parameter

   <source lang="sql">
  

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.

 </source>
   
  

round dbms_utility.get_time

   <source lang="sql">
  

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>

 </source>
   
  

This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.

   <source lang="sql">
  

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.

 </source>
   
  

time to commit

   <source lang="sql">
  

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

 </source>
   
  

Timing Package function call

   <source lang="sql">
  

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>

 </source>
   
  

Timing Per Thousand Records Processed (in secs)

   <source lang="sql">
  

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>

 </source>
   
  

use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.

   <source lang="sql">
  

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>

 </source>
   
  

Use dbms_utility.get_time to do performace check

   <source lang="sql">
 

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>

 </source>
   
  

Use dbms_utility to time

   <source lang="sql">
  

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

 </source>