Array/List of numbers in oracle procedure or function

Sometimes you may need to pass a list of numbers or string into a oracle procedure or function. But there is no built in support for an array or list in oracle. To solve this type of problem you can create your own array type and a small utility function which returns an array of numbers from a comma(,) delimited string of numbers. Here are the steps -

1. Create an array type

CREATE OR REPLACE TYPE array AS TABLE OF NUMBER;

2. Create the utility function which will return array of numbers from an comma delimited string of numbers.

CREATE FUNCTION get_array(num_list IN VARCHAR2)

RETURN array PIPELINED

IS

    countAppearance NUMBER := 0;

    delimiter_position NUMBER := -1;

    start_position NUMBER := 1;

    num VARCHAR2(10);

 

BEGIN

    WHILE delimiter_position != 0 LOOP

        countAppearance := countAppearance + 1;

        delimiter_position := InStr(num_list, ',', 1,countAppearance);

        IF delimiter_position = 0 THEN

            num := Trim(SubStr(num_list,start_position));

        ELSE

            num := Trim(SubStr(num_list,start_position,(delimiter_position - start_position)));

            start_position := delimiter_position + 1;

        END IF;

 

        PIPE ROW(TO_NUMBER(num));

 

    END LOOP;

    RETURN;

END get_array;

 

Here is a sample function which uses this get_array function -

CREATE OR REPLACE FUNCTION testFunc(num_list IN VARCHAR2)

RETURN NUMBER

IS

   total NUMBER := 0;

BEGIN

   SELECT

      COUNT(*) INTO total

   FROM

      testTable

   WHERE

      age IN (SELECT * FROM TABLE(get_array(num_list)));

 

   RETURN total;

END testFunc;

 

In the same way you can also create array for string. It will be helpful when you will need to pass an array from a programming language to a oracle function/procedure.