Convert numbers to words(positional)

To convert numbers to words, for example 1 to first, 2 to second etc.

SQL> SELECT TO_CHAR(TO_DATE(1, 'J'),'JSPTH') FROM dual;

TO_CH
-----
FIRST

SQL> SELECT TO_CHAR(TO_DATE(2, 'J'),'JSPTH') FROM dual;

TO_CHA
------
SECOND

Display Used/Free space in Oracle Tablespaces

Introduction
To get tablespace used/free space information, we can use below script.

Column MBS_ALLOCATED represent total current size of the tablespace in Mbs.
Column MBS_FREE represent total free size of the tablespace in Mbs.
column MBS_USED represent total used size of the tablespace in Mbs.
column PCT_FREE represent total free size in precentage.
column PCT_USED represent total used size in precentage.
column MAX represent tablespace can be extend upto maximum size.
column USAGE_GRAPH display used size in 10 blocks.

column TABLESPACE_NAME format a20
column MBS_ALLOCATED format 9,999,999.99
column MBS_FREE format 9,999,999.99
column MBS_USED format 9,999,999.99
column PCT_FREE format 999.99
column PCT_USED format 999.99
column MAX format 9,999,999.99
column USAGE_GRAPH format a11

select x.*,
rpad('*',round((mbs_used*100/mbs_allocated)/10), '*') usage_graph
--trim(rpad(rpad(' ',round((mbs_used*100/mbs_allocated)/10)+1, chr(7)), 11, chr(8))) usage_graph
from (
-- for NON-TEMP tablespaces
select  a.tablespace_name,
  round(a.bytes_allocated / 1024 / 1024, 2) mbs_allocated,
  round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) mbs_free,
  round((a.bytes_allocated - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) mbs_used,
  round((nvl(b.bytes_free, 0) / a.bytes_allocated) * 100,2) Pct_Free,
  100 - round((nvl(b.bytes_free, 0) / a.bytes_allocated) * 100,2) Pct_used,
  round(maxbytes/1048576,2) Max
from  ( select  f.tablespace_name,
          sum(f.bytes) bytes_allocated,
          sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
   from dba_data_files f
   group by tablespace_name) a,
 ( select  f.tablespace_name,
          sum(f.bytes)  bytes_free
   from dba_free_space f
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
-- for TEMP tablespace
select h.tablespace_name,
  round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_allocated,
  round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
  round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
  round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
  100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
  round(f.maxbytes / 1048576, 2) max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name, f.maxbytes) x
ORDER BY tablespace_name;

TABLESPACE_NAME      MBS_ALLOCATED      MBS_FREE      MBS_USED PCT_FREE PCT_USED           MAX USAGE_GRAPH
-------------------- ------------- ------------- ------------- -------- -------- ------------- -----------
CWMLITE                      20.00         10.63          9.38    53.13    46.87     32,767.98 *****
DRSYS                        20.00         10.31          9.69    51.56    48.44     32,767.98 *****
EXAMPLE                     148.75           .13        148.63      .08    99.92     32,767.98 **********
INDX                         96.25          1.13         95.13     1.17    98.83     32,767.98 **********
OCC                          50.00         14.13         35.88    28.25    71.75         50.00 *******
ODM                          20.00         10.69          9.31    53.44    46.56     32,767.98 *****
SYSTEM                      490.00          5.19        484.81     1.06    98.94     32,767.98 **********
TEMP                         40.00         40.00           .00   100.00      .00     32,767.98
TOOLS                        10.00          3.94          6.06    39.38    60.62     32,767.98 ******
UNDOTBS1                    205.00        190.69         14.31    93.02     6.98     32,767.98 *
USERS                       142.50          1.06        141.44      .75    99.25     32,767.98 **********
XDB                          38.13           .19         37.94      .49    99.51     32,767.98 **********

12 rows selected.

SQL>

Formatted SQL Execution Plan

To see Explain Plan result in the formatted way, oracle provided a PL/SQL package called "dbms_xplan". Below is the example, which explain how to use this package.

SQL> DELETE FROM plan_table WHERE statement_id = 'SACH';

4 rows deleted.

SQL>
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'SACH'
3 FOR
4 select * from scott.emp where deptno = '10';

Explained.

SQL> /

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.DISPLAY('PLAN_TABLE','SACH'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | EMP         |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("EMP"."DEPTNO"=10)

Note: rule based optimization

14 rows selected.

Array (PL/SQL Table) to String Conversion Utility

Introduction

This package consists of reusable functions, which will provide the facility to convert from delimited string to array (PL/SQL tables) and from array (PL/SQL tables) to delimited string.

For example: The comma separated string, ‘Red,Blue,Yellow’, needs to be converted into an array namely x. After conversion, the length of array x will be 3 and the value of the array x will be as follows:

x(1) = ‘Red’
x(2) = ‘Blue’
x(3) = ‘Yellow’

Usage

This package consists of the following three functions:

First function namely fn_string_to_varchararray, requires two parameters. The first parameter is a varchar2 data type, which needs to be converted into an array (PL/SQL tables). The second parameter is an optional varchar2 data type parameter and the default value is comma, which gives the flexibility to use any delimiter.

Second function namely fn_varchararray_to_string, requires three parameters. The first parameter is an array of tab_varchar2_array type, which needs to be converted into a string. The second parameter is an optional varchar2 data type parameter and the default value is NULL. The third parameter is an optional varchar2 data type parameter and the default value is comma, which gives the flexibility to use any delimiter.

Third function namely fn_numberarray_to_string, requires three parameters. The first parameter is an array of tab_varchar2_array type, which needs to be converting into a string. The second parameter is an optional varchar2 data type parameter and the default value is NULL. The third parameter is an optional varchar2 data type parameter and the default value is comma, which gives the flexibility to use any delimiter.

Requirement
Create the following Types:

CREATE TYPE TAB_VARCHAR2_ARRAY AS TABLE OF VARCHAR2 (4000);
CREATE TYPE TAB_NUMBER_ARRAY AS TABLE OF NUMBER;

Create the following Package:

CREATE OR REPLACE PACKAGE pkg_array_utility
IS
  /*************************************************************************************************
  Purpose :- This package contains function and procedure to manipulate Array(PL/SQL Tables).
  Author  :- Sachin Chauhan
  Creation Date :- 26-DEC-2007
  Change Summary: -
  --------------------------------------------------------------------------------------------------
  S. No.  Date      Author                              Description
  --------------------------------------------------------------------------------------------------
  1.
  2.
  *************************************************************************************************/
  FUNCTION fn_string_to_varchararray (
    i_sv_list        IN   VARCHAR2,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN tab_varchar2_array;

  FUNCTION fn_varchararray_to_string (
    i_var_array      IN   tab_varchar2_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2;

  FUNCTION fn_numberarray_to_string (
    i_var_array      IN   tab_number_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_array_utility
IS
  FUNCTION fn_string_to_varchararray (
    i_sv_list        IN   VARCHAR2,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN tab_varchar2_array
  IS
    /***********************************************************************************************
    Purpose :- This function is used for converting string into array (pl/sql table).
    Author  :- Sachin Chauhan
    Creation Date :- 26-DEC-2007
    Change Summary: -
    ------------------------------------------------------------------------------------------------
    S. No.  Date      Author                              Description
    ------------------------------------------------------------------------------------------------
    1.
    2.
    ***********************************************************************************************/
    l_pls_idx        PLS_INTEGER;
    l_sv_list        VARCHAR2 (32767)          := i_sv_list;
    l_tab_splittbl   tab_varchar2_array := tab_varchar2_array ();
    l_pls_del_len    PLS_INTEGER               := LENGTH (i_sv_delimiter);
  BEGIN
    IF l_sv_list IS NOT NULL THEN
      LOOP
        -- search for delimiter string
        l_pls_idx := INSTR (l_sv_list, i_sv_delimiter);
        -- increase the size of array
        l_tab_splittbl.EXTEND;

        -- check last search of delimiter is success
        IF l_pls_idx = 0 THEN
          l_tab_splittbl (l_tab_splittbl.COUNT) := SUBSTR (l_sv_list, 1);
        ELSE
          l_tab_splittbl (l_tab_splittbl.COUNT) := SUBSTR (l_sv_list, 1, l_pls_idx - 1);
        END IF;

        -- exit from loop when last string
        EXIT WHEN NVL (l_pls_idx, 0) = 0;
        l_sv_list := SUBSTR (l_sv_list, l_pls_idx + l_pls_del_len);
      END LOOP;
    END IF;

    RETURN l_tab_splittbl;
  END fn_string_to_varchararray;

  FUNCTION fn_varchararray_to_string (
    i_var_array      IN   tab_varchar2_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2
  IS
    /***********************************************************************************************
    Purpose: - This function is used to convert array of varchar2 to string.
    Author  :- Sachin Chauhan
    Creation Date :- 26-DEC-2007
    Change Summary:-
    ------------------------------------------------------------------------------------------------
    S. No.  Date      Author                              Description
    ------------------------------------------------------------------------------------------------
    1.
    2.
    ***********************************************************************************************/
    l_sv_string   VARCHAR2 (32767);
    l_sv_delim    VARCHAR2 (100);
  BEGIN
    IF i_var_array.COUNT > 0 THEN
      FOR i IN i_var_array.FIRST .. i_var_array.LAST
      LOOP
        -- to avoide delimiter after last string
        IF i = 2 THEN
          l_sv_delim := i_sv_delimiter;
        END IF;

        l_sv_string :=
             l_sv_string
          || l_sv_delim
          || CASE
               -- add 'NULL' as string instead of null
             WHEN i_var_array (i) IS NULL THEN 'NULL'
               ELSE i_sv_enclose || i_var_array (i) || i_sv_enclose
             END;
      END LOOP;
    END IF;

    RETURN (l_sv_string);
  END;

  FUNCTION fn_numberarray_to_string (
    i_var_array      IN   tab_number_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2
  IS
    /***********************************************************************************************
    Copyright      : Copyright © P&G 

    Purpose: - This function is used to convert vararray of number to string.
    Author  :- Sachin Chauhan
    Creation Date :- 26-DEC-2007
    Change Summary:-
    ------------------------------------------------------------------------------------------------
    S. No.  Date      Author                              Description
    ------------------------------------------------------------------------------------------------
    1.
    2.
    ***********************************************************************************************/
    l_sv_string   VARCHAR2 (32767);
    l_sv_delim    VARCHAR2 (100);
  BEGIN
    IF i_var_array.COUNT > 0 THEN
      FOR i IN i_var_array.FIRST .. i_var_array.LAST
      LOOP
        -- to avoide delimiter after last string
        IF i = 2 THEN
          l_sv_delim := i_sv_delimiter;
        END IF;

        l_sv_string :=
             l_sv_string
          || l_sv_delim
          || CASE
               -- add 'NULL' as string instead of null
             WHEN i_var_array (i) IS NULL THEN 'NULL'
               ELSE i_sv_enclose || TO_CHAR (i_var_array (i)) || i_sv_enclose
             END;
      END LOOP;
    END IF;

    RETURN (l_sv_string);
  END;
END;
/

Examples

1. Converting from String to Array (PL/SQL Tables) in PL/SQL block

DECLARE
  l_sv_string   VARCHAR2 (50) := 'Red,Blue,Yellow';
  l_sv_arr      tab_varchar2_array := tab_varchar2_array ();
BEGIN
  l_sv_arr := pkg_array_utility.FN_STRING_TO_VARCHARARRAY
                                                         (l_sv_string);
  DBMS_OUTPUT.put_line (l_sv_arr(1));
  DBMS_OUTPUT.put_line (l_sv_arr(2));
  DBMS_OUTPUT.put_line (l_sv_arr(3));
END;
The Result of PL/SQL block will be:

Red
Blue
Yellow

2. Converting from Varchar2 Array (PL/SQL Tables) to String in PL/SQL block

DECLARE
  l_sv_arr      tab_varchar2_array := tab_varchar2_array ();
  l_sv_string   VARCHAR2 (50);
BEGIN
  l_sv_arr.EXTEND;
  l_sv_arr (1) := 'Red';
  l_sv_arr.EXTEND;
  l_sv_arr (2) := 'Blue';
  l_sv_arr.EXTEND;
  l_sv_arr (3) := 'Yellow';
  l_sv_string := pkg_array_utility.fn_varchararray_to_string (l_sv_arr);
  DBMS_OUTPUT.put_line (l_sv_string);
  l_sv_string := pkg_array_utility.fn_varchararray_to_string (l_sv_arr, '''');
  DBMS_OUTPUT.put_line (l_sv_string);
  l_sv_string := pkg_array_utility.fn_varchararray_to_string (l_sv_arr, '''', '^');
  DBMS_OUTPUT.put_line (l_sv_string);
END;

The Result of PL/SQL block will be:

Red,Blue,Yellow
'Red','Blue','Yellow'
'Red'^'Blue'^'Yellow'

3. Converting from Varchar2 Array (PL/SQL Tables) to String in SQL

SELECT pkg_array_utility.fn_varchararray_to_string
          (CAST (MULTISET (SELECT   column_name
                               FROM dba_tab_columns
                              WHERE owner = 'SCOTT' AND table_name = 'EMP'
                           ORDER BY column_id) AS tab_varchar2_array))
  FROM DUAL;

The Result of SQL will be:

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

Note: Example number 2 and 3 can be used for number data type using the fn_numberarray_to_string function.

Convert Numbers into Words using PL/SQL

Introduction

This function will provide the facility to convert a number to words. This function can accept value one to duodecillion. It also accept decimal value, if the decimal value is more than 2 digit then it will use upto 2 decimal only.

This is a improved version of Tom Kyte solution.

Create below procedure:

CREATE OR REPLACE function spell_number( p_number in number)
return varchar2
as
  type myArray is table of varchar2(20);
  l_str    myArray := myArray( '',
                         ' thousand ', ' million ',
                         ' billion ', ' trillion ',
                         ' quadrillion ', ' quintillion ',
                         ' sextillion ', ' septillion ',
                         ' octillion ', ' nonillion ',
                         ' decillion ', ' undecillion ',
                         ' duodecillion ' );

  l_num varchar2(50) default trunc( p_number );
  l_dec varchar2(50);
  l_return varchar2(4000);
begin
  for i in 1 .. l_str.count loop
    exit when l_num is null;
    if ( substr(l_num, length(l_num)-2, 3) <> 0 ) then
      l_return := to_char(
                    to_date(
                      substr(l_num, length(l_num)-2, 3),
                            'J' ),
                     'Jsp' ) || l_str(i) || l_return;
    end if;
    l_num := substr( l_num, 1, length(l_num)-3 );
  end loop;
  if instr(p_number, '.') > 0 then
    --l_dec := substr(p_number, instr(p_number, '.')+1);
    l_dec := substr(ltrim(to_char(p_number-trunc(p_number), '.99')), 2);
    --dbms_output.put_line(l_dec);
    l_return := ltrim(rtrim(l_return)) || ' and ' || ltrim(rtrim(spell_number(l_dec)));
  end if;
  return replace(l_return, '-', ' ');
end;
/

Usage

SQL> select spell_number(1342344322332) from dual;

SPELL_NUMBER(1342344322332)
--------------------------------------------------------------------------------
One trillion Three Hundred Forty Two billion Three Hundred Forty Four million Th
ree Hundred Twenty Two thousand Three Hundred Thirty Two