Never Assume, always test…

Yesterday evening I started preparing for the upcoming Oracle PL/SQL Programming Test-A-Thon. I strongly believe that building your own programs to perform a functionality, which is already provided as a Built-In package is plain silly. Sometimes however, the Built-In functionality is not what you expect it to be. For instance the DBMS_UTILITY.COMMA_TO_TABLE procedure. This procedure takes a comma-delimited string and returns an Associative Array (a.k.a PL/SQL tables or Index-By Tables). Too bad that this Built-In functionality only works on legal PL/SQL identifiers.

I thought it would be a good idea to set up a series of testcases, so that when a new version of Oracle is shipped I could run all these tests again and check if the implementation of this procedure is still the same. This would make it easy to see if the Built-In packaged functionality was changed.
If only they would enhance the functionality, e.g. allowing a list of comma-delimited numbers to be passed in as a parameter, so that I won’t have to write those string parsers anymore….

The function that I want to use to test the DBMS_UTILITY.COMMA_TO_TABLE would take a comma delimited string of first- and last names and would return a Nested Table Type. Using a Nested Table Type as the return parameter, I could also use this function in straight SQL using the TABLE operator, as in:

select *
   from TABLE (function);

Having said that, first I needed an Object Type (for the first- and the last name) and a Nested Table Type based on this Object

create or replace type name_type is object (firstname varchar2(20), lastname varchar2(20))
/
create or replace type name_list_t is table of name_type
/

Next was the function needed to convert the comma-delimited string into this Nested Table:

1  create or replace function names (p_name in varchar2)
2     return name_list_t
3  is
4     retval name_list_t;
5     ibt dbms_utility.uncl_array;
6     idx  pls_integer;
7     v varchar2(1000);
8     b binary_integer;
9     pos integer;
10 begin
11    v := replace (p_name, ' ', '#');
12    retval := name_list_t();
13    dbms_utility.comma_to_table (v,b,ibt);
14    idx := ibt.first;
15    while idx is not null
16    loop
17       retval.extend;
18       pos:= instr(ibt(idx),'#');
19       retval(retval.last) := name_type( substr(ibt(idx),1,pos-1),substr(ibt(idx),pos+1));
20       idx := ibt.next(idx);
21    end loop;
22    return retval;
23 end names;

In line 13 of the code above I am using the DBMS_UTILITY.COMMA_TO_TABLE procedure to convert the comma-delimited string to an Associative Array. In lines 14 through 21 this Assocative Array is changed into the Nested Table Type that we were after in the first place. Why not leave it the Associative Array as it was? Well, an Associative Array does not exist in the SQL realm, in other words, you cannot do this with an Associative Array:

select *
  from TABLE (<Associative Array>)
;

The First Test Case

Just to check if the function was callable from SQL*Plus, I opened up SQL*Plus and issued:

 SQL> select *
  2*   from table (names ('John ,Paul ,George ,Ringo '))
SQL> /

FIRSTNAME            LASTNAME
-------------------- --------------------
John
Paul
George
Ringo

So far, so good. Now for a more formal way of testing this code.
What other tool to create a Test Case than to use QCTO, Quest Code Tester for Oracle? More on this tool in future blogs, as this blog is not on how to use QCTO or how much I like it 😉

Knowing that QCTO needs to have a function that will test for equality, because of the composite nature of my output parameter, I need a function like this:

create or replace function names_eq (p_names1_i in name_type
                                    ,p_names2_i in name_type
                                    ,nullsequal in boolean
                                    )
   return boolean
is
begin
   return ((p_names1_i.firstname = p_names2_i.firstname
            or (p_names1_i.firstname is null and p_names2_i.firstname is null)
           )
           and (p_names1_i.lastname = p_names2_i.lastname
                or (p_names1_i.lastname is null and p_names2_i.lastname is null)
               )
          );
end names_eq;

As I created my first testcase with the first names of The Beatles, I found jet another oddity with the DBMS_UTILITY.COMMA_TO_TABLE functionality (apart from the legal identifier names).

I would expect to have a collection of four elements returned by this function, being the names I provided. Similar to this:

e_Function_return_value1 := name_list_t();
e_function_return_value1.extend(4);
e_Function_return_value1(1) := name_type ('John', null);
e_Function_return_value1(2) := name_type ('Paul', null);
e_Function_return_value1(3) := name_type ('George', null);
e_Function_return_value1(4) := name_type ('Ringo', null);

However, there was no smiley after running the code…

Did you notice that? The highlighted line in the screenshot? Apparently the function is returning a collection of five elements, while my string only contains four names. But what about what I say in SQL*Plus? Just four rows returned… or is it?

SQL> ed
Wrote file afiedt.buf

  1  select rownum
  2       , firstname
  3       , lastname
  4*   from table (names ('John ,Paul ,George ,Ringo '))
SQL> /

    ROWNUM FIRSTNAME            LASTNAME
---------- -------------------- --------------------
         1 John
         2 Paul
         3 George
         4 Ringo
         5

No, there are five rows returned by the function… There you have it, SQL*Plus is NOT the way to test your code!
There definitely is a bug in my code. The DBMS_UTILITY.COMMA_TO_TABLE procedure has one IN-type parameter and two OUT-type parameters.
One of the out parameters is the Associative Array (named TAB), after the string has been parsed, and the other one gives information on the number of elements in the Associative Array (named TABLEN).
Oddly enough, the number of elements in the Associative Array is one greater than the TABLEN parameter tells you. Using the Looping method as shown in the above function, you would actually see this element.
If you would use a numeric FOR LOOP construction, you will only see the elements that you expected all along….

   for i in 1..b
   loop
      retval.extend;
      pos:= instr(ibt(i),'#');
      retval(retval.last) := name_type( substr(ibt(i),1,pos-1),substr(ibt(i),pos+1));
   end loop;

Conclusion

Never make assumptions on the code you write or use.

Documentation

In the documentation, you can find a reference to this “feature”:
“A PL/SQL table is returned, with values 1..n and n+1 is null.”