Got "ORA-01722: invalid number" while running the trigger

We load data from table TRITON_OUDW.LZ_OM_AR_DETAIL to TRITON.DV_OM_AR_DETAIL
using minus merge and have 1 Row level trigger on table TRITON.DV_OM_AR_DETAIL
that Insert/update the History table TRITON.DV_OM_AR_DETAIL_HST

Below error we got while loading data into TRITON.DV_OM_AR_DETAIL and trigger is fired that got this error :-

-20000-ORA-20000: Updating: :OLD.DV_ETL_JOB_EXEC_PLAN_MEMBER_ID=2128411, :OLD.ORIGIN = AR :OLD.LINE_ID = 103309735 :OLD.DIST_ID = 887766577 - ORA-01722: invalid number ORA-06512: at "TRITON.DV_OM_AR_DETAIL_UDI", line 331 ORA-04088: error during execution of trigger 'TRITON.DV_OM_AR_DETAIL_UDI'


---------------------------------------------------------------------
Steps Tried:-

1. Tried to get only error record in DV and disable all the columns.

Solution :-

https://community.oracle.com/thread/3903593

http://stackoverflow.com/questions/19273610/to-charnumber-function-returns-ora-01722-invalid-number

TO_NUMBER strange behavior in SQL Developer

Assumption:-
we have column ORDER_NUMBER that has some non numeric data present in it with numeric ones.

Observation:-
if we selected  select  TO_NUMBER(ORDER_NUMBER) AS ORDER_NUMBER
from table_name  in sql developer.

The above statement can give you error "ORA-01722: invalid number"  because the SQL Developer selected randomly 50 records and display them on screen.

So if we have any of the records that have non-numeric data then we will get the error and if we got all the numeric order_number then we will not get the error.


But if we use any order by or group by clause then we will definitely get the error because then oracle process them in all one set .



Thanks
Nawneet

Tips Design Application that Interact with Database

Tips Design Application that Interact with Database

Today while looking into the code of the project that takes data from fronthand and then process it to different system I notice  few things :-

1.  The data should not be based on the order of the data but on the final status of it:- It means that while we take data from any front hand always take think of all the input the user can put the data in underlying tables and we need to define only few as minimum input ways as possible to have clear understanding of the data.
2. Always has the history table of the front-hand tables , so that we know how the users are trying to insert data into the screen.