Trigger Error

[ramkrish72]

Hi All,
Please do find mentioned the trigger error below.I'm
encountering with this error:
CUS_QUAL_BANK update
ORA-06502: PL/SQL: numeric or value error: character
to number conversion error
ORA-06512: at "MRM2A.TR_PRE_UPD_CUS_QUAL_BANK", line 5
ORA-04088: error during execution of trigger
'MRM2A.TR_PRE_UPD_CUS_QUAL_BANK'
This trigger is for audit tables-Pre update trigger.Pls do help me to find
out whats wrong with this error.Pls do find enclosed the attached trigger creation script for this trigger.

Thanks for ur attention.Your support is greatly
appreciated.The schema is as below for both the normal table and audit tables.

DESC CUS_QUAL_BANK;
Name Null? Type
---------------------------------------- -------- ----------------------------
RCRE_USER_ID NOT NULL VARCHAR2(15)
RCRE_DT NOT NULL DATE
LCHG_USER_ID VARCHAR2(15)
LCHG_DT DATE
INT_ID NOT NULL NUMBER(10)
PRG_CD NOT NULL VARCHAR2(5)
CREDIT_ID NOT NULL NUMBER(8)
CREDIT_TYPE NOT NULL VARCHAR2(2)
YRS_CREDITED NOT NULL NUMBER(2)
MTHS_CREDITED NOT NULL NUMBER(2)
AWD_TIER_BONUS_FLG NOT NULL VARCHAR2(1)
CARD_ISSUED_BY_SQ_FLG NOT NULL VARCHAR2(1)
RESET_MILEAGE_SECTOR_FLG VARCHAR2(1)
CREDIT_STATUS_IND NOT NULL VARCHAR2(2)

SQL> DESC CUS_QUAL_BANK_AUD;
Name Null? Type
---------------------------------------- -------- ----------------------------
FLD_ID NOT NULL VARCHAR2(35)
DT_TIME_STAMP NOT NULL DATE
UPD_DEL_IND NOT NULL VARCHAR2(1)
AUDIT_USER_ID NOT NULL VARCHAR2(15)
INT_ID NOT NULL NUMBER(10)
PRG_CD NOT NULL VARCHAR2(5)
CREDIT_ID NOT NULL NUMBER(8)
OLD_VALUE VARCHAR2(20)
NEW_VALUE VARCHAR2(20)

Thanks,
Rama

[jmodic]

Jees, how blind can som peple be?!?!

The error message is telling you exactly what is wrong and where it is wrong! The column CREDIT_ID is declared as NUMBER(8), yet in line 5 of your trigger you are comparing this numeric column to character 'x' in your NVL() calls.

[tamilselvan]

=====================================
Jees, how blind can som peple be?!?!
====================================
Yes Jurij.

I had countless argument with many developers. They do not think how oracle handles data types, expect oracle should do conversion dynamically.

Here is a simple example:

SQL> create table t1 (id number) ;

Table created.

SQL> insert into t1 values (10);

1 row created.

SQL> declare x number ;
  2  begin
  3  select id into x from t1 ;
  4  if nvl(x,'x') <> 'x' then
  5    dbms_output.put_line ('not equal') ;
  6  end if;
  7  end;
  8  /
declare x number ;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

SQL> set serveroutput on size 100000
SQL> get x
  1  declare x number ;
  2  begin
  3  select id into x from t1 ;
  4  if nvl(x,0) <> 0 then
  5    dbms_output.put_line ('not equal') ;
  6  end if;
  7* end;
SQL> /
not equal

PL/SQL procedure successfully completed 

Tamil