MERGE ... and value too large
For my reporting I create a table with multiple columns and then fill those columns one-by-one with a MERGE clause.
In the test phase this has worked perfectly and so I planned to base a significant part of the processing on this syntax. However, I am now testing with an increased volume of data and find that, when I remove my testing filters I get the message:
"ORA-01401: inserted value too large for column"
At first this confused me but I have whittled down my query and find that when I remove the filter ...
WHERE PLANT_CODE='Y'
... I get the ORA-01401 error.
Removing this filter means that I am no longer retrieving data for just a single 'plant' but instead the whole company.
I find that the initial INSERT (to the table) has no problem when I remove the PLANT filter. The problem only comes when I try to remove the filter from a MERGE statement.
I have tried adding a COMMITs after each statement but this didn't solve the problem.
I made sure that the fields selected and used for the join (in the MERGE) are primary key fields.
The SELECT clause within the MERGE statement is a join on three tables (all indexed) so I am wondering whether I have hit a capacity limit in Oracle? Is MERGE a 'delicate' operation that can only work on simple joins? Or can I do more complicated things.
The SELECT clause is a GROUP BY statement. Can this create problems?
Anyway, as I mentioned, this is a bit confusing because the syntax works perfectly until I remove the filter (and thereby increase the data volume).
Any tips would be a great help.
Regards and thanks,
Alan Searle