8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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
- 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:
Hope this helps. Regards Tim...