INDEX BY VARCHAR2 problem

[Ivan C.]

Greetings!

I've discovered a new problem while debugging a procedure that uses a PL/SQL table indexed by VARCHAR2.

Here's the test procedure to reproduce the problem:

CREATE OR REPLACE
PROCEDURE a_test IS
   TYPE t IS TABLE OF NUMBER
      INDEX BY VARCHAR2 (10);
   n t;
   --
   v VARCHAR2 (10);
BEGIN
   n('ONE')        := 1;
   n('TWENTYTWO')  := 22;
   n('one')        := .1;
   n('twenty two') := .22;
   --
   v := n.FIRST;
   WHILE v IS NOT NULL
   LOOP
      dbms_output.put_line (v || ':' || n(v));
      --
      v := n.NEXT (v);
   END LOOP;
END;

If you simply execute this procedure (SERVEROUTPUT ON), the result will be:

ONE:1
TWENTYTWO:22
one:.1
twenty two:.22

Now, try debugging this procedure...
Put a break point at line 13, and run it. When the execution stops at the break point, do the following:
1. Put the cursor over

n('ONE')

and the pop-up will display "n(ONE) = 1" (which is correct).
2. Put the cursor over

n('TWENTYTWO')

and the pop-up will display "n('TWENTYTWO') = 22" (which is also correct)
3. Put the cursor over

n('one')

and the pop-up will display "n('one') = 1". This is incorrect! It should display "n('one') = .1"
4. Put the cursor over

n('twenty two')

No pop-up will show, and I have reasons to suspect that the space is the cause of the issue in this case.

Now, if you add the above lines (1, 2, 3 and 4) to the "Variable watch list" (at the bottom of the "debug window"), you'll get the same results.

After further investigation, here are my conclusions:
1. The VARCHAR2 index MUST be upper case at least during the first assignment, for PL/SQL Developer to be able to read it:
Example:

n('One') := .1;
   n('ONE') := 1;

If you stop before executing the second line, and try to "see" value for n('One'), PL/SQL Developer will show "n('One') = (Not a variable)". After executing the second line, PL/SQL Developer will display THE SAME VALUE for both n('One') and n('ONE');

2. There MUST be no spaces in the VARCHAR2 index value for PL/SQL Developer to be able to read it.

Note that Oracle PL/SQL doesn't have a problem interpreting these values. n('one') and n('ONE') are two different records in a PL/SQL table (see the above results when running the procedure).

Please fix this.

Thank you beforehand.
Regards,
Ivan C.

 

[Marco Kalter]

We'll check it out and will fix it!