MERGE to table with Virtual Columns + Error Log Table = ORA-03113
This is just in case you come across this. It appears to be an Oracle bug. I don’t know if it’s been fixed in a later release. I haven’t isolated a simple repeatable test case yet, so I don’t know if the scenario here includes all the relevant details or not.
Version: Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 – 64bit Production
- MERGE into a table that has one or more virtual columns.
- The MERGE statement has a “LOG ERRORS” clause.
- The error log table is a standard one created using DBMS_ERRLOG.create_error_log.
Other factors that may or may not be involved are:
- the target table is in a different schema
- the merge statement queries another table with the same name in the current schema
- the merge statement includes WHEN MATCHED THEN UPDATE, DELETE WHERE, WHEN NOT MATCHED THEN INSERT cluases
- the merge statement has a WHERE clause on the WHEN NOT MATCHED clause
ORA-03113: end-of-file on communication channel and/or ORA-03114: not connected to ORACLE
This happens whether the MERGE is executed or just explain-plan’ed – so it shouldn’t be related to the actual data in either the source or destination tables.
After creating the log table, but before the merge, drop the columns from the error log that are virtual columns in the target table.