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.

Take care with NOT IN Oracle

Below data copied from (http://jonathanlewis.wordpress.com/2007/02/25/not-in/)



The “NOT IN” operator can cause confusion – especially when you have nulls lurking in critical columns, and most particularly where subqueries are involved

You can avoid most of the problems if you just remember that “NOT IN” is not the opposite of “IN”. Here’s an example to explain why.

colX in (value1, value2, value3)
means

      colX = value1
or    colX = value2
or    colX = value3
When Oracle is evaluating this predicate, it only takes one of the three components to evaluate to true for the entire statement to evaluate to true.

colX NOT in (value1, value2, value3)
means

      colX != value1
AND   colX != value2
AND   colX != value3
When Oracle is evaluating this predicate, every single one of the components must evaluate to true for the entire predicate to evaluate to true.

But if one of the values involved is a NULL, then that component evaluates to neither true nor false, it evaluates to null – so the whole expression evaluates to null.

So NULL values are allowed and don’t affect the outcome of IN predicates, but do affect the outcome of “NOT IN” predicates. You’ll also notice that in general IN predicates can short-circuit – the tests can stop on the first success (true), but the same is not true for NOT IN. [But see comment 2 and comment 4 below.

ORA-08103: Object No Longer Exists

Finally I am able to find the root cause for ORA-08103: object no longer exists.

Root Cause Analyses:-
       During executing of select statement if any other session has truncated the underling table used     in select statement ,we got the error ORA-08103: object no longer exists.
 Example :-   Procedure PROC_1 ties to insert into table TABLE_1  which is refers  table TABLE_2.
                      At the same time some other refresh has truncated the table TABLE_2.

Solution:-    As a permanent solution, We should keep all the dimension as merge NOT Truncate/Insert. 

Materialize view

Recently i faced a issue that has wasted my 2 days of work on Materialized View.

I have below script that i have created for the MV

CREATE MATERIALIZED VIEW WI_REASON_COUNT_MV
BUILD IMMEDIATE
REFRESH COMPLETE
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
SELECT
T.day_name,
T.WEEK_END_DATE,
T.month_desc,
T.year_id,
T.CAL_QUARTER_DESC,
Ro.RESOURCE_ID,
WRO.PROCESS_ID,
WRO.ORGANIZATION_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
Ro.CHANGEOVER_REASON_ID,
SUM(Ro.NUMBER_OF_CHANGE_OVER) NUMBER_OF_CHANGE_OVER,
SUM(Ro.TOTAL_CHANGE_OVER_TIME ) TOTAL_CHANGE_OVER_TIME ,
SUM(Ro.NUMBER_OF_SETUP) NUMBER_OF_SETUP ,
SUM(Ro.TOTAL_SETUP_TIME) TOTAL_SETUP_TIME,
SUM(Ro.TRGT_CHANGE_OVER_PLANT) TRGT_CHANGE_OVER_PLANT,
SUM(Ro.TRGT_CHANGE_OVER_LINE) TRGT_CHANGE_OVER_LINE ,
SUM(Ro.TRGT_CHANGE_OVER_PROCESS) TRGT_CHANGE_OVER_PROCESS,
SUM(Ro.TRGT_SETUP_PLANT) TRGT_SETUP_PLANT ,
SUM(Ro.TRGT_SETUP_LINE)TRGT_SETUP_LINE,
SUM(Ro.TRGT_SETUP_PROCESS) TRGT_SETUP_PROCESS,
SUM(Ro.SCHEDULED_HRS) SCHEDULED_HOURS,
SUM(Ro.MAN_HOURS) MAN_HOURS,
COUNT(Ro.NUMBER_OF_CHANGE_OVER),
COUNT(Ro.NUMBER_OF_SETUP) ,
COUNT(Ro.TRGT_CHANGE_OVER_PLANT),
COUNT(Ro.TRGT_CHANGE_OVER_LINE),
COUNT(Ro.TRGT_CHANGE_OVER_PROCESS),
COUNT(Ro.TRGT_SETUP_PLANT),
COUNT(Ro.TRGT_SETUP_LINE),
COUNT(Ro.TRGT_SETUP_PROCESS),
COUNT(Ro.SCHEDULED_HRS),
COUNT(Ro.MAN_HOURS),
count(*),
GRoUPING_ID (
Ro.RESOURCE_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
Ro.CHANGEOVER_REASON_ID)
FRoM
WI_REASON_COUNT Ro,
WI_SHIFT_CALENDAR_MV T,
WI_RESOURCE_MASTER WRO
WHERE
Ro.SHIFT_ID = T.SHIFT_ID AND
WRO.RESOURCE_ID=Ro.RESOURCE_ID AND
WRO.RESOURCE_ID=T.RESOURCE_ID
GRoUP BY GROUPING SETS ((
Ro.RESOURCE_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
WRO.PROCESS_ID,
WRO.ORGANIZATION_ID,
Ro.CHANGEOVER_REASON_ID,
T.day_name,
T.WEEK_END_DATE,
T.month_desc,
T.year_id,
T.CAL_QUARTER_DESC)


I go the duplicate data in MV for single entry in my base table WI_REASON_COUNT.
Later i found that this was due to the null value for one of the CHANGEOVER_REASON_ID in my base table

So always keep in mind that what ever columns we have group by in MV that cannot have null value.

GRoUP BY GROUPING SETS ((
Ro.RESOURCE_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
WRO.PROCESS_ID,
WRO.ORGANIZATION_ID,
Ro.CHANGEOVER_REASON_ID,
T.day_name,
T.WEEK_END_DATE,
T.month_desc,
T.year_id,
T.CAL_QUARTER_DESC)