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.