-
Thank you for all your input. I tried using trunc function which allows me to insert and update cust table without any error but it does not insert or update cust_interface table.
How do you use the trim function?
here is the description of the tables as requested by the forum:
SQL> desc cust;
Name Null? Type
----------------------------------------- -------- ----------------------------
C_ID NOT NULL VARCHAR2(15)
INST_ID VARCHAR2(15)
PAYROLL_ID_NUM VARCHAR2(25)
COMPANY_NAME VARCHAR2(50)
SALUTATION VARCHAR2(7)
F_NAME NOT NULL VARCHAR2(25)
MI VARCHAR2(1)
L_NAME NOT NULL VARCHAR2(25)
SUFFIX VARCHAR2(7)
EMAIL NOT NULL VARCHAR2(50)
BIRTHDAY DATE
REFERRAL_TYPE_CD VARCHAR2(25)
FOLLOWUP_FLAG NOT NULL NUMBER(1)
SSN VARCHAR2(11)
USERNAME VARCHAR2(25)
PIN VARCHAR2(25)
PAYMENT_INFO_ID NUMBER(10)
HTTP_REFERER VARCHAR2(128)
SALARY NUMBER(8,2)
DOWNLOAD_ID VARCHAR2(25)
BACKEND_ID NUMBER(10)
ACTIVE NUMBER(3)
CHANGED_BY NOT NULL VARCHAR2(25)
CHANGED_DATE NOT NULL DATE
LOOKUP2 VARCHAR2(15)
LOOKUP3 VARCHAR2(15)
ACTION_CODE VARCHAR2(15)
PAY_FREQ VARCHAR2(15)
SQL> desc cust_interface;
Name Null? Type
----------------------------------------- -------- ----------------------------
C_ID VARCHAR2(15)
INST_ID VARCHAR2(15)
F_NAME VARCHAR2(25)
MI VARCHAR2(1)
L_NAME VARCHAR2(25)
LOOKUP2 VARCHAR2(15)
LOOKUP3 VARCHAR2(15)
ACTION_CODE VARCHAR2(15)
PAY_FREQ VARCHAR2(15)
ADDRESS_ID VARCHAR2(15)
ADDRESS_TYPE_CD VARCHAR2(25)
IS_DEFAULT NUMBER(5)
STREET_1 VARCHAR2(50)
STREET_2 VARCHAR2(50)
CITY VARCHAR2(25)
STATE_PROVINCE VARCHAR2(25)
ZIP VARCHAR2(11)
COUNTRY VARCHAR2(50)
COUNTY VARCHAR2(25)
INSERT_TYPE VARCHAR2(3)
TIME_STAMP VARCHAR2(25)
-
I checked all fields and they seems good.
But 1 more detail!!!
We have message:
ora-06512: at webuser, cust_tri_iu line 28 (!!! line = 28)
in triggers source code :
------------------------------------------------
1. CREATE OR REPLACE TRIGGER cust_trig_iu
2. AFTER insert or update on CUST
3. For each row
4. BEGIN
5. IF INSERTING then
6. INSERT into CUST_INTERFACE (
7. d,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
9. VALUES (:new.c_id,:new.f_name,:new.mi,:new.l_name,:new.inst_id,:new.pay_freq,:new.lookup2,:new.lookup3,:new .action_code,
'I',to_char(sysdate,'DD-MON-YY HH24:MM:SS'));
9. ELSE
10. INSERT into CUST_INTERFACE (
c_id,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
11. VALUES (
:new.c_id,:new.inst_id,:new.f_name,:new.mi,:new.l_name,:new.pay_freq,:new.lookup2,:new.lookup3,:new. action_code,
'U',to_char(sysdate,'DD-MON-YY HH24:MM:SS'));
12. END IF;
13. END;
we have less lines.
My question - are u check source code in database or not?
-
What do you mean by less line? How do I check the source code in the database? Is it by quering user_triggers?
-
The columns were listed singly before in SQL*Plus. That's why you see line 28. But now, I joined the columns in a straight line.
-
Hi
There is a problem in this line
6. INSERT into CUST_INTERFACE (
7. d,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
9. VALUES (:new.c_id,:new.f_name,:new.mi,:new.l_name,:new.inst_id,:new.pay_freq,:new.lookup2,:new.lookup3,:new .action_code,
'I',to_char(sysdate,'DD-MON-YY HH24:MM:SS'));
In 7th line dont you think
d should be c_id
Regards
Santosh
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|