Oracle PL/SQL Tutorial/System Packages/dbms utility

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>