How to mock indexer and out parameter in NMock

In my last project I was trying to mock an indexer and some out parameters. But I could not find anything about mocking indexer and out parameter in NMock documentation. Then after some google searches, I have found some good post about these topics.

You can view this blog post on mocking out parameters. I think this post is good enough to learn how to mock out parameter. Now, if you want to mock an indexer then you can see in this post. You can also take a look in this post to know how to mock an indexer in NMock.

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.