CODEWITHSUNDEEP
oracle-databaseplsql
Andrei Maieras
Andrei Maieras

Reputation: 706

NULL index table key value error raised only at first run of statement

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

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:

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;

Upvotes: 2

Views: 16585

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

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:

   FOR rec IN (SELECT DISTINCT NODETYPEID FROM LINKPORTVALIDATION) LOOP
      gr_nodetypes_vld(rec.NODETYPEID) := 1; --this line
   END LOOP;

... when therec. NODETYPEID is 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.

It doesn't actually complain if you use a literal 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.

Upvotes: 3

Related Questions