Utility assortment
ECHO (word-wrapper for DBMS_OUTPUT), TO_DECIMAL, TO_BASE (number conversion functions), SEND_MAIL (HTML formatted email), LIST_ELEMENT (string tokeniser), SPLIT, TO_STRING etc, all in one rather arbitrary package.
-- Various PL/SQL utilities. Help yourself, but credit me occasionally and don't sue me if they break.
-- Written for Oracle 9i. (How time flies.)
-- UTILITIES is a bit of a non-name. For production use, these procedures and functions might be better
-- moved into more tightly defined packages.
--
-- 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 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 := 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
|| '' || k_crlf
|| '' || k_crlf
|| '' || k_crlf
|| '' || k_crlf
|| '' || p_subject || ' ' || k_crlf
|| '' || k_crlf
|| '' || k_crlf
|| p_message || k_crlf
|| '';
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