Itgenpse062 error in PSQL due to missing into since Invantive SQL 17.31.72

Wiki (en)
, , ,
1

Problem

Previously functioning PSQL procedures and functions can raise an itgen062 error “Missing into clause” during compilation starting Invantive SQL version 17.31.72.

Solution

Starting Invantive SQL 17.31.72 we have introduced an improvement to ensure that select statements in PSQL code always return exactly one row and always have an into clause.

These changes were introduced to enforce improved code quality.

Sample

Before this version your code could contain a select statement like:

declare
  p_log_on_code varchar2 := 'sample';
begin
  select raise_error
         ( 'xxive001'
         , 'Connected as user '
            || user
           || ', but should have been connected as '
           || p_log_on_code
           || '.'
         )
  from   dual@DataDictionary
  where  user != p_log_on_code
  ;
end;

However, this statement will no longer compile. The developer needs to change the statement to contain an into clause, such as:

select value(s)
into   PSQL-variable(s);

Applied to the sample above the PSQL code would become:

declare
  p_log_on_code varchar2 := 'sample';
  l_dummy       varchar2;
begin
  select raise_error
         ( 'xxive001'
         , 'Connected as user '
            || user
            || ', but should have been connected as '
            || p_log_on_code
            || '.'
         )
  into   l_dummy
  from   dual@DataDictionary
  where  user != p_log_on_code
  ;
end;

There must be one

This variant will work correctly when the where-clause evaluates to true and exactly one row is returned. However, an itgenpse008-error “Not enough rows.” will be raised when no row is selected.

In the query above, no row is returned when the user equals p_log_on_code. When the query can return no rows, you should rewrite the PSQL code as:

declare
  p_log_on_code varchar2 := 'sample';
begin
  if user != p_log_on_code
  then
    raise_application_error
    ( -20163 /* Some random number typically used by Invantive products. */
    , 'Connected as user '
      || user
      || ', but should have been connected as '
      || p_log_on_code
      || '.'
    );
  end if;
end;

Note that the error code raised changes in this scenario from xxive001 to 20163. Any other number is fine too, but remember that the error code on some platforms such as Oracle maps to a meaning. For instance, on Oracle 1403 means “No data found”.

Alternatively, you can use the if exists construct. This is especially useful when the original select is a non-trivial query like using multiple tables. For example:

declare
  p_log_on_code varchar2 := 'sample';
begin
  if exists
   ( select 1
     from Dual@DataDictionary
     where user != p_log_on_code
   )
  then
    raise_application_error
    ( -20163
    , 'Connected as user '
       || user
       || ', but should have been connected as '
       || p_log_on_code
       || '.'
    );
  end if;
end;

There can only be one

In some cases the select statement within PSQL will return multiple rows. This list of rows was discarded automatically when no into clause was present. However, by adding an into clause you also enforce the rule that there can be at most one row returned. Otherwise an itgenpse009-error “Too many rows” is raised.

The same code rewriting rules apply as explained above.