ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at “OJC.JC_MASTER”, line 129

Question

I'm trying to run an interface in ODI 11g. When I call the procedure I get this error :

ODI-1228: Task START_JC (Procedure) fails on the target ORACLE connection OJC.
Caused By: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "OJC.JC_MASTER", line 129
ORA-06512: at "OJC.JC_MASTER", line 689
ORA-06512: at line 9

the sql code

 PROCEDURE string_to_aa_parameter_type (
   p_string                             VARCHAR2,
   p_out_aa_parameter_values   IN OUT   aa_parameter_type
)
AS
   v_start                  INTEGER         := 1;
   v_pos                    INTEGER         := 0;
   v_counter                INTEGER         := 0;
   v_temp_parameter_name    VARCHAR2 (4000);
   v_temp_parameter_value   VARCHAR2 (4000);
BEGIN
   IF p_string IS NULL
   THEN
      RETURN;
   END IF;

   -- determine first chuck of string
   v_pos := INSTR (p_string, '=', v_start);

   -- while there are chunks left, loop
   WHILE (v_pos != 0)
   LOOP
      v_counter := v_counter + 1;

      -- create array
      IF MOD (v_counter, 2) = 1
      THEN
         v_temp_parameter_name :=
                               SUBSTR (p_string, v_start, v_pos - v_start);
         v_start := v_pos + 1;
         v_pos := INSTR (p_string, ';', v_start);
      ELSE
         v_temp_parameter_value :=
                               SUBSTR (p_string, v_start, v_pos - v_start);

         p_out_aa_parameter_values (trim(v_temp_parameter_name)) :=
                                                    trim(v_temp_parameter_value);

         v_start := v_pos + 1;
         v_pos := INSTR (p_string, '=', v_start);

      END IF;
   END LOOP;

   -- IN THE FOLLOWING LINE I GET THE ERROR 
   v_temp_parameter_value := SUBSTR (p_string, v_start);

   p_out_aa_parameter_values (trim(v_temp_parameter_name)) :=
                                             trim(v_temp_parameter_value);

END;

Can someone help me in figuring out that the problem is ?

How are you calling it, and what are you passing in? And are you sure it's that line and not the next one, which woudl make more sense for the error; v_temp_parameter_name seems to end up null, so is the value you're giving for p_string not null but with no equals sign? — Alex Poole
yes the error comes from the last line . i didn't write the procedure,i have to run it . It is supposed to be syntax correct . — eda
Syntactically correct doesn't mean logically correct or complete. It doesn't seem to be expecting to have to handle the data you're actually sending it. That doesn't even necessarily means it's wrong or broken - whoever did write it might say your code is the problem not theirs. Depends on the agreed specification... — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2018-02-14T15:44:16

You'll get that error if p_string is a not-null value which doesn't contain an equals sign at all, or with any semicolon-delimited part that starts with an equals sign. It's thrown by the line after the one you indicated (or the equivalent line inside the loop, if p_string has a final semicolon).

If there is no equals sign at all then

v_pos := INSTR (p_string, '=', v_start);

gives zero, which means you don't go through the loop at all; which means when you get to that final assignment v_temp_parameter_name has never been set.

If there is a key/value pair with no key, say p_string is 'x=y;=z' you do go into the loop, and with that example the first key/value pair is added to the array; but then v_start and v_pos end up as the same value (5 in this case, both pointing to the second =). The the next time round the loop:

         v_temp_parameter_name :=
                               SUBSTR (p_string, v_start, v_pos - v_start);

evaluates to SUBSTR(p_string, 5, 0) (where the third argument is zero because those two variables are the same), which is always going to be an empty string, or null.

There is no actual error yet, so it evaluates v_pos again, and either gets zero or non-zero, depending on whether there is a terminating semicolon.

If it's non-zero then it goes round the loop again; if it's zero it drops out. Either way it has a last stab at getting the matching value - it doesn't matter if that is set to anything or not. When it tries to add the element to the array, though, the name is still null, and you get that error, from whichever of the two array assignments it hits.

You could do additional testing and handling inside the procedure to spot and discard null keys, but

i didn't write the procedure,i have to run it . It is supposed to be syntax correct

So you need to figure out why the Java code is passing a value which the procedure can't handle - i.e. why it is sending incomplete key/value pairs.

ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at “OJC.JC_MASTER”, line 129

1 Answers