Modify table from itab

[Former Member]

I have a modify statement that is used to insert into a z table

MODIFY Z_TABLE FROM ITAB. ITab has 459780 entries and is of the exact structure as the Z table (7 fields in total) that I am tryin to insert into. But i have got a short dump

Database error text........: "ORA-00604: error occurred at recursive SQL level

1#ORA-04031: unable to allocate 4120 bytes of shared memory ("shared

pool","select i.obj#,i.ts#,i.file#,...","Typecheck","kgghteInit")"

Internal call code.........: "[RSQL/178/ZBW_POS_CTRL ]"

Please check the entries in the system log (Transaction SM21).

On reading help on the modify statement, I learnt that there is a limit on the number of entries that can be inserted/changed in a single LUW. But half a million records is hardly a huge number to give me a dump isn't it ? Doesn't anyone have any ideas why ????

[Former Member]

Hi,

Whether the DB can handle it or not, it is not a good practice to insert/update half a million records in one shot.

Having had too many unpleasant exchanges with DBAs, I have cultivated the habit of submitting a commit after every 100,000 changes to a DB table.

Apparently, change log table gets filled up and becomes a big nuisance if a rollback occurs.

DESCRIBE TABLE t_zceb_fica_gl_ext LINES sy-tfill.

IF sy-tfill < p_maxins.

IF g_subrc = c_unprocessed_keys_found.

INSERT zceb_fica_gl_ext FROM TABLE t_zceb_fica_gl_ext

ACCEPTING DUPLICATE KEYS.

ELSE.

INSERT zceb_fica_gl_ext FROM TABLE t_zceb_fica_gl_ext.

ENDIF.

ELSE.

REFRESH t_insert_chunk.

LOOP AT t_zceb_fica_gl_ext ASSIGNING <fs_zceb_fica_gl_ext>.

ADD 1 TO l_count.

APPEND <fs_zceb_fica_gl_ext> TO t_insert_chunk.

IF l_count >= p_maxins.

IF g_subrc = c_unprocessed_keys_found.

INSERT zceb_fica_gl_ext FROM TABLE t_insert_chunk

ACCEPTING DUPLICATE KEYS.

ELSE.

INSERT zceb_fica_gl_ext FROM TABLE t_insert_chunk.

ENDIF.

COMMIT WORK.

CLEAR l_count.

REFRESH t_insert_chunk.

ENDIF.

ENDLOOP.

*& Now, insert the remaining last chunk of records

IF g_subrc = c_unprocessed_keys_found.

INSERT zceb_fica_gl_ext FROM TABLE t_insert_chunk

ACCEPTING DUPLICATE KEYS.

ELSE.

INSERT zceb_fica_gl_ext FROM TABLE t_insert_chunk.

ENDIF.

ENDIF.

COMMIT WORK.

Reward if helpfull...

Cheers,

Rakesh.