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
DBMS_RANDOM
DBMS_UTILITY.is_cluster_database
DBMS_UTILITY.active_instances
DBMS_UTILITY.current_instance
DBMS_UTILITY.db_version
DBMS_UTILITY.port_string
DBMS_UTILITY.comma_to_table & table_to_comma

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:

Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2)

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)