ORA-06502:PL/SQL:数字或值错误:NULL 索引表键值 ORA-06512:在“OJC.JC_MASTER"，第 129 行 [英] ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "OJC.JC_MASTER", line 129

问题描述

我正在尝试在 ODI 11g 中运行一个接口.当我调用该程序时，出现此错误:

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

ODI-1228:任务 START_JC(程序)在目标 ORACLE 连接 OJC 上失败.
引起:java.sql.SQLException:ORA-06502:PL/SQL:数字或值错误:NULL 索引表键值
ORA-06512:在OJC.JC_MASTER"，第 129 行
ORA-06512:在OJC.JC_MASTER"，第 689 行
ORA-06512:在第 9 行

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

sql 代码

 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 ?

推荐答案

如果 p_string 是一个完全不包含等号的非空值，你会得到那个错误，或者任何以等号开头的分号分隔部分.它由您指示的行(或循环内的等效行，如果 p_string 有最后一个分号)之后的行抛出.

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

如果根本没有等号，则

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

给出零，这意味着你根本不经过循环；这意味着当您完成最终分配时，v_temp_parameter_name 从未设置过.

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.

如果有一个没有键的键/值对，比如 p_string 是 'x=y;=z' 你确实进入了循环，然后例如，第一个键/值对被添加到数组中；但随后 v_start 和 v_pos 最终成为相同的值(在本例中为 5，都指向第二个 =).下一次循环:

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);

计算结果为 SUBSTR(p_string, 5, 0)(其中第三个参数为零，因为这两个变量相同)，它始终为空字符串或 null.

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.

目前还没有实际错误，因此它再次评估 v_pos，并根据是否存在终止分号而得到零或非零.

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

所以你需要弄清楚为什么 Java 代码传递了一个程序无法处理的值 - 即为什么它发送不完整的键/值对.

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:数字或值错误:NULL 索引表键值 ORA-06512:在“OJC.JC_MASTER"，第 129 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！