Never Assume, always test…

8

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.”

 

Share.

About Author

8 Comments

  1. Alex Nuijten on

    Thank you for your comments Bart!
    In the blog I also point to the documentation where it says that an extra row is returned (right under “Documentation”).
    I think we’re basically on the same page regarding DBMS_OUTPUT – by creating your own wrapper for DBMS_OUTPUT, you are enhancing Built-in functionality. (by the way; during the last session of OPP which was a Q&A with the PL/SQL product managers they are going to provide an overloading for BOOLEANs)
    I am aware of the limitations of DBMS_UTILITY.COMMA_TO_TABLE- last line of the first paragraph: “Too bad that this Built-In functionality only works on legal PL/SQL identifiers.” But you are correct, USER is a legal identifier but cannot be used in DBMS_UTILITY.COMMA_TO_TABLE.
    Mainly this post was about Assumptions and Testing. I made the false assumption that DBMS_UTILITY.COMMA_TO_TABLE would return 4 elements, when in fact it returned 5 elements.

  2. I left a sentence unfinished:

    ‘Clearly it must be something’

    I was trying to say that user is a function returning the current user and it looks like the dbms_utility.comma_to_table is not handling that like a literal.

  3. Take a look at this: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_util2.htm#1002109

    It is clearly documented that de pl/sql table returns an extra row (see the Returns description).

    But what’s more interesting is that when you have a person named ‘user’.

    Did you try that one? I get a ORA-00931: missing identifier when I wanted to translate ‘user,all,dba’ into a table.
    Clearly it must be something

    I definitely disagrees on extending (I don’t think dbms_output.put_line is overloaded – why should it be? – Oracle does the implicit necessary -and sometimes unexpected- casting?) dbms_output.put_line to support booleans. What do you expect it to output? What value represents True ? 0, -1, 1 T, True ???

    What is easier and cleaner than to make you own (truly) overloaded version of dbms_output.put_line ?

    Best Regards,

    Bart Verstegen,
    Berkel en Rodenrijs.

  4. Thank you for your comments, Empresas de Peru!
    I agree with you along the lines of enhancing Built-In functionality, absolutely. Like enhancing DBMS_OUTPUT to also show BOOLEANs.
    But trying to come up with something which already is provided by the Built-In packages Oracle provides, no that would be silly.
    Example: Why would you want to build your own Scheduler functionality, while Oracle already provides DBMS_JOB or DBMS_SCHEDULER?

  5. “building your own programs to perform a functionality, which is already provided as a Built-In package is plain silly”

    Not really, IMO it all depends on several factors. For example, if the function you are trying to use is restricted somehow, or it doesn’t perform “everything” you need it to, you can build upon the existing one, or entirely create one yourself. How about security holes? It’s less likely for my app to be hacked if it’s made by oneself, than one used by thousands of persons, where the hacking potential grows exponentially… just some food for thought… ;)

  6. Alex Nuijten on

    Thanks guys!

    I was asked to assist Steven with the Test-A-Thon during the conference, so I can’t compete myself. But I am sure it will be great fun.