Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Show the row that causes error in "insert .. select"
Hello,
When we use "INSERT ... SELECT" statement even if one row is not acceptable for the table whole statement is rolling back and almost there is no information about the row that cause to error. this error could be, unique constraint violation, too large value, not null constraint etc. I know that there is a dml error logging mechanism but it is not easily implement all the time, especially if insert statement is coming from application and application has no any structure to define it.
it is also something that developers deal with all the time and it would be much more easier if while Oracle was generating this error, could give the information about that row. of course in a "perfect" system shouldn't have this kind of errors but there so many systems that design in many years ago by people who is not expert on DBs and we have to work based on these systems. so I believe it would make our life much more easier.
thanks.
Comments
-
-
You should be able to do that using the LOG ERROR clause.
Please check Oracle SQL Reference: https://docs.oracle.com/database/121/SQLRF/statements_9015.htm#BGBDIGAH
HIH,
-
You should be able to do that using the LOG ERROR clause.
Please check Oracle SQL Reference: https://docs.oracle.com/database/121/SQLRF/statements_9015.htm#BGBDIGAH
HIH,
Hi,
I already know that: "I know that there is a dml error logging mechanism but it is not easily implement all the time" from my first post, but as I said it is not easily implement all the time, it would be much better if errored row would be a part of error message. that's what I suggest.
-
How would you "show the row" ? I fail to see an approach that covers this in proper detail.
I could imaging to show the object/column that violated some rule. And show the offending value. But "show the row" seems not possible to me.
Image tables that have blob columns or nested table columns in them. How would you "show" that?
-
How would you "show the row" ? I fail to see an approach that covers this in proper detail.
I could imaging to show the object/column that violated some rule. And show the offending value. But "show the row" seems not possible to me.
Image tables that have blob columns or nested table columns in them. How would you "show" that?
I thought about it too, there might be restrictions on it, even so, it could be just the value of the column that cause to the error but important point is giving a detail about it. if "insert .. select" takes 1 hour and after that 1 hour I got an error message, I have to re run this select to find that row. there should be something that help me to identify problem faster.
-
I thought about it too, there might be restrictions on it, even so, it could be just the value of the column that cause to the error but important point is giving a detail about it. if "insert .. select" takes 1 hour and after that 1 hour I got an error message, I have to re run this select to find that row. there should be something that help me to identify problem faster.
Please don't misunderstand me. I felt the same pain sometimes.
They only sensible way I see is to store the row somewhere in an extra table And store the error and the constraint that was violated along with it..
Hm.. wait. Isn't that excatly what DML_ERROR_LOG already does?
So maybe the suggestion should be to "auto enable" all tables in a schema to use DML- error logging. And maybe simplify the process to create those log table.
Like
Alter table xyz enable error_logging;
-
Please don't misunderstand me. I felt the same pain sometimes.
They only sensible way I see is to store the row somewhere in an extra table And store the error and the constraint that was violated along with it..
Hm.. wait. Isn't that excatly what DML_ERROR_LOG already does?
So maybe the suggestion should be to "auto enable" all tables in a schema to use DML- error logging. And maybe simplify the process to create those log table.
Like
Alter table xyz enable error_logging;
Hi Sven,
I agree it might be an "auto enable" error logging structure but first, I shouldn't be changing the insert statements to log the error, as I mentioned in my claim, sometimes insert statements are coming from the main application and our developer team says "that this statement was created automatically by entity, so they can not change it". I don't want someone has to do something while an dml operation has errors. that is why I am asking it would be nice if values about the that row become the part of error message. I also don't know which statement will has an error and when has an error so if standard dml error logging has an extra performance cost, I don't want it enable all the time.
As you already said there should be some limitations about objects or LOBs etc but how often we are suffering because of these types? this could be ignored in my opinion.
-
Hi,
SQL is designed as a "set" processing program.
The whole "set" succeeds of fails... it sounds maybe weird but this is also the reason why SQL can process a huge amount of data.
If you really what to know what row causes your insert ... select * to fail, you will need to switch to PL/SQL and the use of a cursor to catch up the row.
Doing this will severy impact performance when you need to insert a big "set".
A Meguerian-Oracle, posted an elegant option.
Here is an axample on how to use this feature: https://oracle-base.com/articles/10g/dml-error-logging-10gr2
Kind Regards
-
Hi,
SQL is designed as a "set" processing program.
The whole "set" succeeds of fails... it sounds maybe weird but this is also the reason why SQL can process a huge amount of data.
If you really what to know what row causes your insert ... select * to fail, you will need to switch to PL/SQL and the use of a cursor to catch up the row.
Doing this will severy impact performance when you need to insert a big "set".
A Meguerian-Oracle, posted an elegant option.
Here is an axample on how to use this feature: https://oracle-base.com/articles/10g/dml-error-logging-10gr2
Kind Regards
I am an Oracle consultant and trainer around 8 years, I know what sql do and do not. I also, as many other pro does not, does not want to use (switch) to plsql, I already wrote the reasons about that, if you read please.
