Oracle PL/SQL Tutorial/System Packages/dbms utility

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>