ORA-01722 invalid number where clause in update statement. I want to log this error in error log table

5 pts.
Tags:
ORA
Oracle 10g

Oracle ora-01722 invalid number where clause in update statement. I want to log this error in error log table. How can i log?? Below is my Update statement, here empid is number.

UPDATE SARVESH_TEST_ERR_TB SET empid = empid WHERE empid ='aaa' LOG ERRORS INTO SARVESH_ERR_LOGS_TB ('Error while UPDATE') REJECT LIMIT UNLIMITED;

If we gibe set empid = 'abc' where empid =105 , this error is logging in error log table. But if i am giving a string in where clause it is not logging,



Software/Hardware used:
SOftware

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    The LOG ERRORS clause's objective is to not stop (and rollback) the execution of a statement because of data errors when performing the DML operation (inserts, updates, deletes).

    When you try to update a numeric field with a non-numeric string, the UPDATE is actually tried and thus the error is logged, but when you use a non-numeric string in the WHERE clause which causes a type mismatch when searching for the records, the UPDATE is never tried because the engine is not even able to identify the records that should be affected, and that is probably the reason why the error is not logged.

    In that case you would probably need to put the UPDATE in a BEGIN-EXCEPTION-END block and log the error from the exception handler. 

    I must say, however, that it doesn't make much sense to me to try to use the LOG ERRORS clause for errors like that, which are clearly programming errors that will cause the code to fail every time it is run.
    69,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following