SAPSQL_ARRAY_INSERT_DUPREC inserting unique records into empty DB table

[matt]

So, we've got code like this:

DATA my_data type hashed table of ztable with unique key keyfield.
...
DELETE FROM ztable. "#EC CI_NOWHERE
INSERT ztable FROM TABLE my_data.

And at the insert, we're getting the dump SAPSQL_ARRAY_INSERT_DUPREC.

Database is Oracle. ztable contains ~200 records before the delete, my_data is of a similar size.

[matt]

So, we've got code like this:

DATA my_data type hashed table of ztable with unique key keyfield.
...
DELETE FROM ztable. "#EC CI_NOWHERE
INSERT ztable FROM TABLE my_data.

And at the insert, we're getting the dump SAPSQL_ARRAY_INSERT_DUPREC.

Database is Oracle. ztable contains ~200 records before the delete, my_data is of a similar size.

[RaymondGiuseppi]

• Are there duplicates in your own internal table (is keyfield the only primary key with client in db table ?)
• Isn't the actual statement INSERT ztable FROM TABLE my_data.

[Sandra_Rossi]

Database unique index, database triggers, ...

[matt]

raymond.giuseppi corrected, thank you.

No, there are no duplicates in my internal table. There can't be - it's hashed. The key of the internal table is identical to the key of the db table.

No database triggers.

[RaymondGiuseppi]

• What are the technical settings of the ztable,
• Are there some unique secondary indexes
• Does a call of db_commit FM between the two statements solve the problem (but why would it be required...)

[SimoneMilesi]

I think you need a commit work after the delete because, otherwise, the command is still between your report and the database.

If you follow in debug the code, stopping 1-2 seconds after the delete, you still have the error?

[RobertVit]

commit in between is missing

[matt]

Why should you need a commit within the same database session? If it were true, then the following code would be useless:

DELETE FROM ztable. "#EC CI_NOWHERE
IF sy-subrc IS INITIAL.
  INSERT ztable FROM TABLE my_data ACCEPTING DUPLICATE ROWS.
  IF sy-subrc IS INITIAL.
    COMMIT WORK.
  ELSE.
    ROLLBACK WORK.
  ENDIF.
ELSE.
  ROLLBACK WORK.
ENDIF.

If the insert failed, we'd be left with an empty table, which is not what we want.

[matt]

I'm beginning to think it's a timing issue. But we should be able to stack SQL - otherwise the rollback mechanism is pointless - we'd be unable to do this:

DELETE FROM ztable. "#EC CI_NOWHERE
IF sy-subrc IS INITIAL.
  INSERT ztable FROM TABLE my_data ACCEPTING DUPLICATE ROWS.
  IF sy-subrc IS INITIAL.
    COMMIT WORK.
  ELSE.
    ROLLBACK WORK.
  ENDIF.
ELSE.
  ROLLBACK WORK.
ENDIF.

Maybe I should put the db updates into an update FM.

[former_member378318]

The internal table maybe of type hash with a unique key but it could create a duplicate if the DB table key is not equivalent.

[matt]

It is identical.

[RaymondGiuseppi]

If you really want to always prevent any exception, you may be required to read current data in a old_records_itab and match it with new records to identify records to create, update, delete or ignore, then execute the 3 statements. Many update FM expect such internal table as parameters.

Meanwhile, did you check values of sy-subrc and sy-dbcnt after each abap sql statement?

[matt]

The reason for the dump is that the table was buffered, and it wasn't synced across application servers.

Thanks all for your thoughts.