Itgensql169: Row is required to retrieve string value of variable 'p_actord_code' at index 7. (p_actord_code)

Questions
, ,
1

When executing a PSQL module on Invantive Cloud, I receive always the following error:

itgensql169:
Row is required to retrieve string value of variable ‘p_actord_code’ at index 7. (p_actord_code).

How do I fix this?

The code contains p_actord_code in:

declare
...
  p_account_code                varchar2 := null;
...
and    actord.code     = coalesce(p_actord_code, actord.code)
...
--
-- Filter on ordering customer code and/or name.
--
and    ( p_actord_code is null 
         or 
         ( p_actord_code is not null 
           and sie.orderedby 
               in 
               ( select /*+ low_cost */ act.id 
                 from   accountsBulk@eol act 
                 where  act.code = p_actord_code
               )
         )
       )
2

The construct:

    --
    -- Filter on ordering customer code and/or name.
    --
    and    ( p_actord_code is null 
             or 
             ( p_actord_code is not null 
               and sie.orderedby 
                   in 
                   ( select /*+ low_cost */ act.id 
                     from   accountsBulk@eol act 
                     where  act.code = p_actord_code
                   )
             )
           )

uses the low_cost hint which executes very early at module start, before the module is set up. The value is only available on the next SQL-batch.

Recommendation is to not use low_cost with a parameter in the where-clause, but to rephrase to:

declare
  l_orderedby guid;
begin
  ...
    --
    if p_actord_code is not null
    then
      select act.id 
      into   l_orderedby
      from   accountsBulk@eol act 
      where  act.code = p_actord_code
      ;
    else
      l_orderedby := null;
    end if;
...
    --
    -- Filter on ordering customer code and/or name.
    --
    and    ( p_actord_code is null 
             or 
             ( p_actord_code is not null and sie.orderedby = l_orderedby )
           )

This avoids the too early execution of the low_cost marked sub-query.

The error code could be more clear and concise to indicate the cause. We will study a better alternative.