JR34393: ORACLE EE STAGE UPSERT DOES NOT REPORT CORRECTLY ORACLE RETURN CODES IF INSERT CONTAINS A SELECT STATEMENT THAT RETURNS 0 ROWS

APAR status

• Closed as program error.

Error description

• Oracle Enterprise Stage upsert does not report oracle return
  codes correctly if the insert statement contains a select
  statement that returns 0 rows.
  
  Job logs give this incomplete entry:
  
  Unable to insert a record into the table due to .
  

Local fix

• This fix is included in 8.0.1 fixpack 3
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Users using Oracle EE Stage upsert with insert DML containing
  where clauses.
  ****************************************************************
  PROBLEM DESCRIPTION:
  The row insertion logic does not recognize the Oracle Error
  ORA-01403. As a result Oracle Enterprise Stage upsert does not
  report oracle return  codes correctly if the insert statement
  contains a select statement that returns 0 rows.  Job logs give
  this incomplete entry:
        Unable to insert a record into the table due to .
  ****************************************************************
  RECOMMENDATION:
  Apply patch provided
  This change is included in 8.1 Fix Pack 1.
  ****************************************************************
  

Problem conclusion

• Now we do recognize this ORA-01403  with this fix.
  
  But this fix has limitation that it is possible to cater for an
  ORA-01403 error condition when the processing insertions with an
  array size of 1.  This is due to the fact that there is no
  reliable way to know what exact record in an array has failed
  with this error as Oracle does not treat 'no rows found
  condition' as a DML error
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R810 PSN

     UP