Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Useful Procedures and Functions

Oracle comes with a whole host of supplied packages which cover a vast range of functionality. In this article I'll list a few procedures and functions you may have overlooked which can come in useful during development:

DBMS_LOCK.sleep

The DBMS_LOCK.sleep procedure is used to pause a program for the specified number of seconds. The time can be specified down to hundredths of a second.

BEGIN
  DBMS_LOCK.sleep(seconds => 5.01);
END;
/

DBMS_RANDOM

More information about DBMS_RANDOM can be seen in a separate article here.

The DBMS_RANDOM package is used to produce random numbers. In Oracle 9i the random number generator should be initialized with a suitably large seed before it is used and terminated once it's no longer needed. Several functions can be used to return random numbers.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_seed  BINARY_INTEGER;
BEGIN
  l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
  DBMS_RANDOM.initialize (val => l_seed);
  FOR cur_rec IN 1 ..10 LOOP
    DBMS_OUTPUT.put_line('----');
    DBMS_OUTPUT.put_line('value                      : ' || TO_CHAR(DBMS_RANDOM.value));
    DBMS_OUTPUT.put_line('value(low => 1, high => 10): ' || TO_CHAR(DBMS_RANDOM.value(low => 1, high => 10)));
  END LOOP;
  DBMS_RANDOM.terminate;
END;
/

From Oracle 10g Release 1 onwards, initialization and termination are no longer necessary as calls to DBMS_RANDOM automatically initialize the seed using the date.

-- Oracle 10g Release 1 Upwards.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN 1 ..10 LOOP
    DBMS_OUTPUT.put_line('----');
    DBMS_OUTPUT.put_line('value                      : ' || TO_CHAR(DBMS_RANDOM.value));
    DBMS_OUTPUT.put_line('value(low => 1, high => 10): ' || TO_CHAR(DBMS_RANDOM.value(low => 1, high => 10)));
  END LOOP;
END;
/

DBMS_UTILITY.is_cluster_database

The DBMS_UTILITY.is_cluster_database function can be used to identify if the current session is running on a cluster.

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_UTILITY.is_cluster_database THEN
    DBMS_OUTPUT.put_line('Clustered');
  ELSE
    DBMS_OUTPUT.put_line('Not Clustered');
  END IF;
END;
/

DBMS_UTILITY.active_instances

The DBMS_UTILITY.active_instances procedure can be used to identify the active instances in the cluster.

SET SERVEROUTPUT ON
DECLARE
  l_instance_table  DBMS_UTILITY.instance_table;
  l_instance_count  NUMBER;
BEGIN
  DBMS_UTILITY.active_instances (instance_table => l_instance_table,
                                 instance_count => l_instance_count);

  IF l_instance_count > 0 THEN
    FOR i IN 1 .. l_instance_count LOOP
      DBMS_OUTPUT.put_line(l_instance_table(i).inst_number || ' = ' || l_instance_table(i).inst_name);
    END LOOP;
  END IF;
END;
/

DBMS_UTILITY.current_instance

The DBMS_UTILITY.current_instance function returns the current instance number.

SELECT DBMS_UTILITY.current_instance
FROM   dual;

DBMS_UTILITY.db_version

The DBMS_UTILITY.db_version procedure returns database version information.

SET SERVEROUTPUT ON
DECLARE
  l_version  VARCHAR2(100);
  l_compatibility  VARCHAR2(100);
BEGIN
  DBMS_UTILITY.db_version (version       => l_version,
                           compatibility => l_compatibility);
  DBMS_OUTPUT.put_line('Version: ' || l_version || '  Compatibility: ' || l_compatibility);
END;
/

DBMS_UTILITY.port_string

The DBMS_UTILITY.port_string function returns the operating system and the TWO TASK PROTOCOL version of the database.

SELECT DBMS_UTILITY.port_string
FROM   dual;

DBMS_UTILITY.comma_to_table & table_to_comma

The DBMS_UTILITY.comma_to_table and DBMS_UTILITY.table_to_comma procedures allow you to split and rejoin the values in a CSV record.

SET SERVEROUTPUT ON
DECLARE
  l_list1   VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
  l_list2   VARCHAR2(50);
  l_tablen  BINARY_INTEGER;
  l_tab     DBMS_UTILITY.uncl_array;
BEGIN
  DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

  DBMS_UTILITY.comma_to_table (
     list   => l_list1,
     tablen => l_tablen,
     tab    => l_tab);

  FOR i IN 1 .. l_tablen LOOP
    DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
  END LOOP;

  DBMS_UTILITY.table_to_comma (
     tab    => l_tab,
     tablen => l_tablen,
     list   => l_list2);

  DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.