-- Various PL/SQL utilities. Help yourself, but credit me occasionally and don't sue me if they break.
-- Requires Oracle 9i. (See utilities80 for an 8.0 compatible version.)
-- UTILITIES is a bit of a non-name. For production use, these procedures and functions might be better
-- moved into more tightly defined packages e.g. STRING_PKG for string manipulation functions.
--
-- ECHO:         Word-wrapper for DBMS_OUTPUT.PUT_LINE, avoiding 255-char limitation.
--               Also standalone procedure which just calls package version.
-- TO_DECIMAL:   Convert number in another base e.g. hex to deciimal.
-- TO_BASE:      (1): Convert any number from decimal to base specified (default 10)
--               (2): Convert between any two specified bases, e.g. octal to base 26
-- SEND_MAIL:    Uses UTL_SMTP in standard way, except it includes HTML option.
--               I copied HTML headers blindly out of an existing e-mail, so may need adjusting
--               or making smarter for use in other environments.
-- LIST_ELEMENT: Extract element m from a character(n)-separated list:
--               e.g. if LIST is 'x,y,z', LIST_ELEMENT(list,2) returns 'y'.
--               Less efficient than the equivalent SUBSTR(INSTR(...)) equivalent but a lot easier to use.
-- SPLIT:        Convert character-separated list to SQL-defined 'nested table' array (VARCHAR2_TT).
-- TO_STRING     Convert collection to character-separated list (accepts VARCHAR2_TT, NUMBER_TT and INTEGER_TT).
--               Originally I called this 'JOIN', and it still worked despite being an SQL keyword.
--
--               You could CAST a MULTISET subquery as a VARCHAR2_TT collection and then use TO_STRING
--               to convert that collection to a list.
--
--               Note that user-defined aggregates e.g. Tom Kyte's oddly-named STRAGG do this kind of
--               thing more elegantly in 9i and also come with a free analytic version, although there
--               are limitations such as they must be standalone and they can only take one argument.
--               (James Padfield's CONCAT_ALL(CONCAT_EXPR(colname,delimiter)) address the latter.)
--               In 10g, the SQL aggregate function COLLECT builds a collection from a set of values
--               returned by a query, which simplifies this task still further.
--
-- William Robertson 2004 - www.williamrobertson.net

set serverout on size 10000

CREATE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000);
/

CREATE TYPE date_tt AS TABLE OF DATE;
/

CREATE TYPE number_tt AS TABLE OF NUMBER;
/

CREATE TYPE integer_tt AS TABLE OF INTEGER;
/

CREATE OR REPLACE PACKAGE utilities
-- William Robertson 2004 - www.williamrobertson.net
AS
	k_error_code CONSTANT PLS_INTEGER := -20500;

	k_crlf CONSTANT VARCHAR2(2) := CHR(13)||CHR(10);

	-- Use 9i INTERVAL datatype to define dateless TIME 0-24 hours:
	-- e.g:
	-- v_interval TIME := TO_DSINTERVAL('0 12:34:56');
	SUBTYPE time IS INTERVAL DAY (0) TO SECOND (0);

	FUNCTION boolean_to_char
		( p_true BOOLEAN )
		RETURN VARCHAR2
		DETERMINISTIC;

	-- Wrapper for DBMS_OUTPUT.PUT_LINE, avoiding 255-char-per-line limit by wrapping lines:
	PROCEDURE echo
		( p_text    VARCHAR2
		, p_wrap_length  PLS_INTEGER DEFAULT 120 );

	-- Alternative ECHO accepts array and processes each line using ECHO(line,wraplength):
	PROCEDURE echo
		( p_text_collection VARCHAR2_TT
		, p_wrap_length  PLS_INTEGER DEFAULT 120 );

	-- Alternative ECHO accepts Boolean and returns TRUE/FALSE (sorry, no NLS translation yet)
	PROCEDURE echo
		( p_boolean BOOLEAN
		, p_wrap_length  PLS_INTEGER DEFAULT 5 );  -- Standard parameter for compatibility

	-- Convert from any numeric base 2-36 to decimal, e.g. TO_DECIMAL('A',36) = 10
	FUNCTION to_decimal
		( p_source  VARCHAR2
		, p_base    PLS_INTEGER )
		RETURN PLS_INTEGER
		DETERMINISTIC;

	-- Convert from decimal to any numeric base 2-36, e.g. TO_BASE(502574,36) = 'ARSE'
	FUNCTION to_base
		( p_decimal PLS_INTEGER
		, p_base    PLS_INTEGER DEFAULT 10 )
		RETURN VARCHAR2
		DETERMINISTIC;

	-- Convert p_source between any two numeric bases 2-36:
	FUNCTION to_base
		( p_source  VARCHAR2
		, p_from    PLS_INTEGER
		, p_to      PLS_INTEGER )
		RETURN VARCHAR2
		DETERMINISTIC;

	FUNCTION to_number_safe
		( p_candidate_number VARCHAR2 )
		RETURN NUMBER
		DETERMINISTIC
		PARALLEL_ENABLE;

	PROCEDURE start_timer;

	PROCEDURE show_timer;

	PROCEDURE send_mail
		( p_sender     VARCHAR2
		, p_recipient  VARCHAR2
		, p_subject    VARCHAR2
		, p_message    VARCHAR2
		, p_html       BOOLEAN := FALSE );

	-- Return element [n] of a character[m]-separated list.
	FUNCTION list_element
		( p_string VARCHAR2
		, p_element INTEGER
		, p_separator VARCHAR2 DEFAULT ',' )
		RETURN VARCHAR2
		DETERMINISTIC;

	-- Split a string into its elements and return the results as a collection:
	FUNCTION split
		( p_text VARCHAR2
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2_TT
		DETERMINISTIC
		PARALLEL_ENABLE;

	-- Equivalent to 'JOIN' in other languages (opposite of SPLIT):
	-- Write the contents of a collection to single string:
	FUNCTION to_string
		( p_table VARCHAR2_TT
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2
		DETERMINISTIC
		PARALLEL_ENABLE;

	-- NUMBER_TT version of above:
	FUNCTION to_string
		( p_table NUMBER_TT
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2
		DETERMINISTIC
		PARALLEL_ENABLE; 

	-- INTEGER_TT version of above:
	FUNCTION to_string
		( p_table INTEGER_TT
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2
		DETERMINISTIC
		PARALLEL_ENABLE; 
END utilities;
/

show errors

CREATE OR REPLACE PACKAGE BODY utilities
AS
	k_nosuchdate CONSTANT DATE := DATE '0000-01-01';
	
	TYPE integer_to_alpha_table  IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
	TYPE alpha_to_integer_table IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(1);
	g_number_to_alpha INTEGER_TO_ALPHA_TABLE;
	g_alpha_to_number ALPHA_TO_INTEGER_TABLE;

	g_time_value NUMBER;

	FUNCTION nosuchdate
		RETURN DATE
	IS
	BEGIN
		RETURN k_nosuchdate;
	END nosuchdate;


	FUNCTION boolean_to_char
		( p_true BOOLEAN )
		RETURN VARCHAR2
	IS
		v_return_text VARCHAR2(5);
	BEGIN
		IF p_true THEN
			v_return_text := 'TRUE';
		ELSE
			v_return_text := 'FALSE';
		END IF;

		RETURN v_return_text;
	END boolean_to_char;


	-- Safety filter for DBMS_OUTPUT.PUT_LINE:
	PROCEDURE echo
		( p_text    VARCHAR2
		, p_wrap_length  PLS_INTEGER DEFAULT 120 )
	IS
		v_text           VARCHAR2(10000) := TRIM(LTRIM(p_text,CHR(9)||CHR(32)));
		k_wrap_length    CONSTANT PLS_INTEGER := LEAST(p_wrap_length,LENGTH(v_text));
		v_line           VARCHAR2(255);
		v_chop_position  PLS_INTEGER;
		v_done           BOOLEAN := FALSE;
	BEGIN
		v_text := RTRIM(LTRIM(v_text,CHR(9)),' '||CHR(9));

		LOOP
			v_line := SUBSTR(v_text, 1, k_wrap_length);
			v_chop_position := LEAST(k_wrap_length,NVL(LENGTH(v_line),0));

			IF v_line = v_text
			OR LENGTH(v_text) <= k_wrap_length
			OR v_line IS NULL
			OR v_text IS NULL
			THEN
				-- No more chopping required
				v_done := TRUE;

			ELSIF v_line LIKE '%' || CHR(10) || '%' THEN
				-- Retain existing linefeeds:
				v_chop_position := INSTR(v_line, CHR(10)) -1;  -- first linefeed in v_line

			ELSIF v_line LIKE '% %' THEN
				v_chop_position := INSTR(v_line, ' ', -1);  -- last space in v_line
			END IF;

			-- Trim down to end of last whole word:
			v_line := TRIM(SUBSTR(v_line,1,v_chop_position));

			-- Chop [length of v_line] off start of v_message
			-- v_text := TRIM(TRIM(CHR(10) FROM SUBSTR(v_text, v_chop_position +1)));
			v_text := LTRIM(RTRIM(SUBSTR(v_text, v_chop_position +1),CHR(10)),CHR(10));

			DBMS_OUTPUT.PUT_LINE(v_line);

			EXIT WHEN v_done;
		END LOOP;
	END echo;


	PROCEDURE echo
		( p_text_collection VARCHAR2_TT
		, p_wrap_length  PLS_INTEGER DEFAULT 120 )
	IS
		i PLS_INTEGER := p_text_collection.FIRST;
	BEGIN
		WHILE i IS NOT NULL
		LOOP
			ECHO(p_text_collection(i), p_wrap_length);
			i := p_text_collection.NEXT(i);
		END LOOP;
	END echo;


	PROCEDURE echo
		( p_boolean BOOLEAN
		, p_wrap_length  PLS_INTEGER DEFAULT 5 )
	IS
	BEGIN
		ECHO(BOOLEAN_TO_CHAR(p_boolean), p_wrap_length);
	END echo;


	FUNCTION to_base
		( p_decimal PLS_INTEGER
		, p_base    PLS_INTEGER DEFAULT 10 )
		RETURN VARCHAR2
	IS
		v_digit PLS_INTEGER;
		v_decimal_remaining PLS_INTEGER := p_decimal;
		v_result VARCHAR2(100);
	BEGIN

		IF p_base NOT BETWEEN 2 AND 36 THEN
			RAISE_APPLICATION_ERROR
			( k_error_code
			, 'TO_BASE: Invalid base ' || p_base || ': must be in range 1 to 36' );
		END IF;

		WHILE v_decimal_remaining > 0
		LOOP
			v_digit := MOD(v_decimal_remaining,p_base);
			v_result := g_number_to_alpha(v_digit) || v_result;
			v_decimal_remaining := FLOOR(v_decimal_remaining / p_base);
		END LOOP;

		RETURN v_result;
	END to_base;


	FUNCTION to_decimal
		( p_source VARCHAR2
		, p_base   PLS_INTEGER )
		RETURN PLS_INTEGER
	IS
		v_source VARCHAR2(130) := UPPER(p_source);
		v_result PLS_INTEGER := 0;
		v_position PLS_INTEGER := LENGTH(p_source);
	BEGIN
		FOR i IN 0..LENGTH(v_source) -1 LOOP
			v_position := LENGTH(v_source) -i;

			DBMS_OUTPUT.PUT_LINE
			( 'i = ' || i || ': '
			|| g_alpha_to_number(SUBSTR(v_source,v_position,1)) || ' * '
			|| POWER(p_base,i) || ' = '
			|| g_alpha_to_number(SUBSTR(v_source,v_position,1)) * POWER(p_base,i) );
			v_result := v_result + g_alpha_to_number(SUBSTR(v_source,v_position,1)) * POWER(p_base,i);
		END LOOP;

		RETURN v_result;
	END to_decimal;


	FUNCTION to_base
		( p_source  VARCHAR2
		, p_from    PLS_INTEGER
		, p_to      PLS_INTEGER )
		RETURN VARCHAR2
	IS
	BEGIN
		RETURN TO_BASE(TO_DECIMAL(p_source,p_from),p_to);
	END to_base;


	FUNCTION to_number_safe
		( p_candidate_number VARCHAR2 )
		RETURN NUMBER
		DETERMINISTIC
		PARALLEL_ENABLE
	IS
	BEGIN
		RETURN TO_NUMBER(p_candidate_number);
	EXCEPTION
		WHEN VALUE_ERROR THEN
			RETURN TO_NUMBER(NULL);
	END to_number_safe;


	PROCEDURE start_timer
	IS
	BEGIN
		g_time_value := DBMS_UTILITY.GET_TIME;
	END start_timer;


	PROCEDURE show_timer
	IS
		v_elapsed g_time_value%TYPE := DBMS_UTILITY.GET_TIME - g_time_value;
	BEGIN
		DBMS_OUTPUT.PUT_LINE(RTRIM(TO_CHAR(ROUND(v_elapsed/100,2),'99990.09'),'0') || ' seconds');
	END show_timer;


	PROCEDURE send_mail
		( p_sender     VARCHAR2
		, p_recipient  VARCHAR2
		, p_subject    VARCHAR2
		, p_message    VARCHAR2
		, p_html       BOOLEAN := FALSE )
	IS
		k_mailhost CONSTANT VARCHAR2(30) := 'smtp.blueyonder.co.uk';
		v_mail_conn UTL_SMTP.CONNECTION := UTL_SMTP.OPEN_CONNECTION(k_mailhost, 25);

		v_message VARCHAR2(2000);
	BEGIN
		IF p_html THEN
			v_message :=
			'Subject: '  || p_subject || k_crlf
			|| 'Content-Type: text/html; charset=us-ascii' || k_crlf
			|| 'Content-Transfer-Encoding: 7bit' || k_crlf
			|| '<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">' || k_crlf
			|| '<html>'  || k_crlf
			|| '<head>'  || k_crlf
			|| '<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">' || k_crlf
			|| '<title>' || p_subject || '</title>' || k_crlf
			|| '</head>' || k_crlf
			|| '<body>'  || k_crlf
			|| p_message || k_crlf
			|| '</body></html>';
		ELSE
			v_message := 'Subject: ' || p_subject || k_crlf || p_message;
		END IF;

		UTL_SMTP.HELO(v_mail_conn, k_mailhost);
		UTL_SMTP.MAIL(v_mail_conn, p_sender);
		UTL_SMTP.RCPT(v_mail_conn, p_recipient);
		UTL_SMTP.DATA(v_mail_conn, v_message);
		UTL_SMTP.QUIT(v_mail_conn);
	EXCEPTION
		WHEN OTHERS THEN
			RAISE_APPLICATION_ERROR
			( k_error_code
			, 'Could not send e-mail message: ''' || p_message || ''''
			, TRUE );
	END send_mail;


	FUNCTION list_element
		( p_string VARCHAR2
		, p_element INTEGER
		, p_separator VARCHAR2 DEFAULT ',' )
		RETURN VARCHAR2
		DETERMINISTIC
	AS
		k_separator CONSTANT VARCHAR2(50) := NVL(p_separator,',');
		k_string CONSTANT VARCHAR2(4000) := k_separator || p_string;
		v_result VARCHAR2(4000);
		v_startpos INTEGER := INSTR(k_string,k_separator,1,p_element) + LENGTH(k_separator);
	BEGIN
		IF NOT (p_string IS NULL OR p_element IS NULL OR v_startpos = 1)
		THEN
			v_result := TRIM(SUBSTR(k_string,v_startpos));
		END IF;

		RETURN SUBSTR(v_result, 1, INSTR(v_result || k_separator, k_separator) -1);
	END list_element;


	FUNCTION split
		( p_text VARCHAR2
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2_TT
	IS
		i PLS_INTEGER := 1;
		v_next_position PLS_INTEGER;
		v_enclosed BOOLEAN;
		v_text VARCHAR2(2000) := LTRIM(RTRIM(p_text,p_delimiter),p_delimiter);
		v_return_array VARCHAR2_TT := VARCHAR2_TT();
	BEGIN
		WHILE v_text IS NOT NULL LOOP
			v_return_array.EXTEND;

			-- Check whether (remaining) text starts with an encloser (e.g. doublequote):
			v_enclosed := SUBSTR(v_text ,1,1) = p_encloser;

			IF v_enclosed THEN
				v_text := TRIM(SUBSTR(v_text,2));
				v_next_position := INSTR(v_text,p_encloser);
			ELSE
				v_next_position := INSTR(v_text,p_delimiter);
			END IF;
			
			IF v_next_position = 0 THEN
				-- No more delimiters found, so return remaining text
				-- (first strip off any spaces and enclosing quotes)
				v_return_array(i) := RTRIM(v_text, p_encloser||' ');
				EXIT;
			ELSE
				-- Use the portion of the text up to the next delimiter or encloser:
				-- ('v_next_position' has already been worked out)
				v_return_array(i) := TRIM(SUBSTR(v_text,1,v_next_position -1));
				v_text := TRIM(SUBSTR(v_text,v_next_position +1));

				IF v_enclosed THEN
					-- Deal with closing quote by stripping one more character:
					v_text := TRIM(SUBSTR(v_text,2));
				END IF;
			END IF;

			i := i +1;
		END LOOP;

		RETURN v_return_array;
	END split;


	FUNCTION to_string
		( p_table VARCHAR2_TT
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2
	IS
		i PLS_INTEGER := p_table.FIRST;
		v_return_string VARCHAR2(32000);
	BEGIN
		WHILE i IS NOT NULL LOOP
			v_return_string := v_return_string ||
			CASE
				WHEN INSTR(p_table(i),p_delimiter) > 0 THEN p_encloser || p_table(i) || p_encloser
				ELSE p_table(i)
			END || 
			p_delimiter;

			i := p_table.NEXT(i);
		END LOOP;

		RETURN RTRIM(v_return_string, p_delimiter);
	END to_string;


	-- Exact copy of VARCHAR2_TT version except for type of first argument.
	FUNCTION to_string
		( p_table NUMBER_TT
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2
	IS
		i PLS_INTEGER := p_table.FIRST;
		v_return_string VARCHAR2(32000);
	BEGIN
		WHILE i IS NOT NULL LOOP
			v_return_string := v_return_string ||
			CASE
				WHEN INSTR(p_table(i),p_delimiter) > 0 THEN p_encloser || p_table(i) || p_encloser
				ELSE p_table(i)
			END || 
			p_delimiter;

			i := p_table.NEXT(i);
		END LOOP;

		RETURN RTRIM(v_return_string, p_delimiter);
	END to_string;


	-- Exact copy of VARCHAR2_TT version except for type of first argument.
	FUNCTION to_string
		( p_table INTEGER_TT
		, p_delimiter VARCHAR2 DEFAULT ','
		, p_encloser VARCHAR2  DEFAULT NULL )
		RETURN VARCHAR2
	IS
		-- Can cast INTEGER_TT as NUMBER_TT using SQL only.
		-- PL/SQL CAST won't work here (9.2), and overloading does not recognise them as equvalent.
		-- Must try this in 10g.
		v_table NUMBER_TT;
	BEGIN
		SELECT CAST(p_table AS NUMBER_TT) INTO v_table FROM dual;
		RETURN TO_STRING(v_table,p_delimiter,p_encloser);
	END to_string;

BEGIN
	-- Pre-load conversion tables to simplify TO_BASE and TO_DECIMAL functions:
	FOR i IN 0..35 LOOP
		IF i < 10 THEN
			g_number_to_alpha(i) := i;
			g_alpha_to_number(TO_CHAR(i)) := i;
		ELSE
			g_number_to_alpha(i) := CHR(i + 55);
			g_alpha_to_number(CHR(i + 55)) := i;
		END IF;
	END LOOP;
END utilities;
/

show errors


CREATE OR REPLACE PROCEDURE echo
	( p_text    VARCHAR2
	, p_wrap_length  PLS_INTEGER DEFAULT 120 )
AS
BEGIN
	UTILITIES.ECHO(p_text, p_wrap_length);
END echo;
/

show errors

CREATE OR REPLACE FUNCTION list_element
	( p_string VARCHAR2
	, p_element INTEGER
	, p_separator VARCHAR2 DEFAULT ',' )
	RETURN VARCHAR2
	DETERMINISTIC
AS
BEGIN
	RETURN UTILITIES.LIST_ELEMENT(p_string, p_element, p_separator);
END list_element;
/

show errors

CREATE OR REPLACE FUNCTION split
	( p_text VARCHAR2
	, p_delimiter VARCHAR2 DEFAULT ','
	, p_encloser VARCHAR2  DEFAULT NULL )
	RETURN VARCHAR2_TT
AS
BEGIN
	RETURN UTILITIES.SPLIT(p_text, p_delimiter, p_encloser);
END split;
/

show errors

-- Standalone version of VARCHAR2_TT version of TO_STRING:
-- (note that overloading is not possible with standalone functions.
-- For other versions, use UTILITIES.TO_STRING().
CREATE OR REPLACE FUNCTION to_string
	( p_table VARCHAR2_TT
	, p_delimiter VARCHAR2 DEFAULT ','
	, p_encloser VARCHAR2  DEFAULT NULL )
	RETURN VARCHAR2
AS
BEGIN
	RETURN UTILITIES.TO_STRING(p_table, p_delimiter,p_encloser);
END to_string;
/

show errors