This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Show the row that causes error in "insert .. select"

Mustafa_KALAYCI
Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown
edited Apr 25, 2017 4:55AM in Database Ideas - Ideas

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.

ctriebpattonjgChinmayee-Oracle
6 votes

Active · Last Updated

Comments

  • ctrieb
    ctrieb Dipl. - Inf.(FH) WormsMember Posts: 314 Gold Trophy

    Good idea, this will help to analyze the problem.

    Mustafa_KALAYCIChinmayee-Oracle
  • 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,

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown

    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.

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited Apr 25, 2017 1:29PM

    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?

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown

    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.

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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;

    Mustafa_KALAYCI
  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown

    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.

    Sven W.
  • Laury
    Laury Member Posts: 1,672 Silver Badge

    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

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown

    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.