-- 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
         || '<!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