oracle NULL 索引表键值错误仅在第一次运行语句时引发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37592338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NULL index table key value error raised only at first run of statement
提问by Andrei Maieras
I have a view on two different schemas described by this code:
我对此代码描述的两种不同模式有看法：
CREATE OR REPLACE VIEW LINKTYPE AS
SELECT LINKTYPEID
, nvl((select value from translation where translation2dimobject = 14 and objectid = linktypeid and columnid = 1 and localeid = SYS_CONTEXT('CRAMERSESSION', 'LOCALEID')), NAME) NAME
, LINKTYPE2GRAPHICSBITMAP
, LINKTYPE2BROWSERBITMAP
, TABLENAME
, nvl((select value from translation where translation2dimobject = 14 and objectid = linktypeid and columnid = 2 and localeid = SYS_CONTEXT('CRAMERSESSION', 'LOCALEID')), CLASS) CLASS
, nvl((select value from translation where translation2dimobject = 14 and objectid = linktypeid and columnid = 3 and localeid = SYS_CONTEXT('CRAMERSESSION', 'LOCALEID')), DESCRIPTION) DESCRIPTION
, RESOLUTIONBEHAVIOUR
, LENGTHCALLOUT
, ISVISIBLE
, LABEL
from LINKTYPE_M with read only;
Then I'm calling this function from both schemas:
然后我从两个模式调用这个函数：
FUNCTION getLinkTypeID (pis_link_type_name LINKTYPE.NAME%TYPE)
RETURN NUMBER IS
ln_link_type_ID NUMBER;
BEGIN
BEGIN
SELECT linktypeid
INTO ln_link_type_ID
FROM CRAMER.LINKTYPE lnt
WHERE lnt.NAME = pis_link_type_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_link_type_ID := null;
END;
RETURN ln_link_type_ID;
END getLinkTypeID;
like this:
像这样：
declare
ln_link_type_ID number;
begin
ln_link_type_ID := customisations.nr_links_validations.getLinkTypeID('test');
dbms_output.put_line(ln_link_type_ID);
end;
No matter the parameter given to the function from one of the schemas I get the
无论从模式之一给函数的参数我得到
NULL index table key value
NULL 索引表键值
error but only at first run, from the other schema I don't have this problem. I need to mention that the schemas are a little different but the part with above view, function and call of the function is identical. Have anyone had this kind of issue or have any idea why this error appears?
错误，但仅在第一次运行时，从其他模式我没有这个问题。我需要提到的是，模式略有不同，但上述视图、函数和函数调用的部分是相同的。有没有人遇到过这种问题或者知道为什么会出现这个错误？
The stack trace shows that the error is raised from here
堆栈跟踪显示错误是从这里引发的
FOR rec IN (SELECT DISTINCT NODETYPEID FROM LINKPORTVALIDATION) LOOP
gr_nodetypes_vld(rec.NODETYPEID) := 1; --this line
END LOOP;
and gr_nodetypes_vld is declare in the body section and not in the specification:
并且 gr_nodetypes_vld 在 body 部分而不是在规范中声明：
TYPE gt_nodetypes_vld IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
gr_nodetypes_vld gt_nodetypes_vld;
I think this is the initialisation section
我认为这是初始化部分
BEGIN
--First-time code. Executed once per session => Performance
--Fill an array of all nodetypes tracked by the validation table.
--All unique nodetypes in there will be checked against their link types.
--All other nodetypes are not validated at all
gr_nodetypes_vld.DELETE;
FOR rec IN (SELECT DISTINCT NODETYPEID FROM LINKPORTVALIDATION) LOOP
gr_nodetypes_vld(rec.NODETYPEID) := 1;
END LOOP;
gr_cardtypes_vld.DELETE;
FOR rec IN (SELECT DISTINCT NODETYPEID||'.'||CARDTYPEID NODECARD_TYPE
FROM LINKPORTVALIDATION
WHERE CARDTYPEID IS NOT NULL
ORDER BY 1) LOOP
gr_cardtypes_vld(rec.NODECARD_TYPE) := 1;
END LOOP;
END NR_LINKS_VALIDATIONS;
回答by Alex Poole
The error is coming from the initialisation section, not the function you are calling. The first call in a session instantiates the package, which includes executing the initialisation section; for subsequent calls in that session the package is already instantiated, which explains why you only see the error the first time.
错误来自初始化部分，而不是您正在调用的函数。会话中的第一个调用实例化包，包括执行初始化部分；对于该会话中的后续调用，包已经实例化，这解释了为什么您只在第一次看到错误。
In the schema where you get the error, you have entries in the LINKPORTVALIDATION table where NODETYPEID is null. When you loop over them:
在您收到错误的架构中，您在 LINKPORTVALIDATION 表中有条目，其中 NODETYPEID 为空。当你循环它们时：
FOR rec IN (SELECT DISTINCT NODETYPEID FROM LINKPORTVALIDATION) LOOP
gr_nodetypes_vld(rec.NODETYPEID) := 1; --this line
END LOOP;
... when therec. NODETYPEIDis null you're trying to set gr_nodetypes_vld(null)to 1, and it is that null index that is throwing the exception you see.
...当rec. NODETYPEID您尝试将 null 设置gr_nodetypes_vld(null)为 1 时，正是该空索引引发了您看到的异常。
It doesn't actually complain if you use a literal null:
如果您使用文字，它实际上不会抱怨null：
DECLARE
TYPE gt_nodetypes_vld IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
gr_nodetypes_vld gt_nodetypes_vld;
BEGIN
gr_nodetypes_vld(null) := 1;
END;
/
PL/SQL procedure successfully completed.
But it does with a binary integer variable that is null:
但它处理一个为空的二进制整数变量：
DECLARE
TYPE gt_nodetypes_vld IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
gr_nodetypes_vld gt_nodetypes_vld;
null_int binary_integer;
BEGIN
gr_nodetypes_vld(null_int) := 1;
END;
/
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 6
The calls from the two schemas are seeing different results from querying that table, so they behave differently.
来自两个模式的调用在查询该表时看到不同的结果，因此它们的行为不同。
