Hi Pl/Sql Guys,
when we try to run Following trigger, getting error like 01403. 00000 - "no data found".
Trigger is
CREATE OR REPLACE TRIGGER SYNC_OUGR_USER_ADDRESS
AFTER INSERT ON OUGR_USER_ADDRESS FOR EACH ROW
DECLARE
P_CD_ADDR_TYPE OUGR_USER_ADDRESS.CD_ADDR_TYPE%TYPE;
P_AD_CITY OUGR_USER_ADDRESS.AD_CITY%TYPE;
P_NM_NAME TOWN.NM_NAME%TYPE;
P_FL_OVERSEAS OUGR_USER_ADDRESS.FL_OVERSEAS%TYPE;
P_AD_COUNTRY OUGR_USER_ADDRESS.AD_COUNTRY%TYPE;
P_TEMP_CITY VARCHAR2(10);
P_CD_CODE REF_COUNTRY_CODE.CD_CODE%TYPE;
BEGIN
P_CD_ADDR_TYPE := :NEW.CD_ADDR_TYPE;
P_FL_OVERSEAS := :NEW.FL_OVERSEAS;
P_AD_CITY := :NEW.AD_CITY;
P_AD_COUNTRY := :NEW.AD_COUNTRY;
SELECT LENGTH(TRIM(TRANSLATE(P_AD_CITY, ' +-.0123456789',' '))) INTO P_TEMP_CITY FROM DUAL; ---to check whether value is numeric
SELECT NM_NAME INTO P_NM_NAME FROM TOWN WHERE ID_TOWN = P_AD_CITY;
SELECT CD_CODE INTO P_CD_CODE FROM REF_COUNTRY_CODE WHERE CD_CODE= P_AD_COUNTRY;
IF P_CD_ADDR_TYPE in ('MA','PA') THEN
IF P_TEMP_CITY IS NULL THEN
P_AD_CITY := P_NM_NAME;
ELSE
P_AD_CITY := P_AD_CITY;
END IF;
ELSE
P_AD_CITY := P_NM_NAME;
END IF;
IF P_FL_OVERSEAS = 'Y' THEN
P_AD_COUNTRY := P_CD_CODE;
ELSE
P_AD_COUNTRY := P_AD_COUNTRY;
END IF;
INSERT INTO OUGR_USER_ADDRESS@TO_GVRS
(ID_ADDRESS,
CD_ADDR_TYPE,
AD_UNIT,
AD_NUM,
AD_STR1,
AD_STR2,
AD_CITY,
AD_COUNTY,
AD_ST,
AD_COUNTRY,
AD_ZIP5,
AD_ZIP4,
FL_AD_RURAL,
FL_OVERSEAS,
TM_STAMP
)
VALUES (:NEW.ID_ADDRESS,
:NEW.CD_ADDR_TYPE,
:NEW.AD_UNIT,
:NEW.AD_NUM,
:NEW.AD_STR1,
:NEW.AD_STR2,
P_AD_CITY,
:NEW.AD_COUNTY,
:NEW.AD_ST,
P_AD_COUNTRY,
:NEW.AD_ZIP5,
:NEW.AD_ZIP4,
:NEW.FL_AD_RURAL,
:NEW.FL_OVERSEAS,
:NEW.TM_STAMP
);
END SYNC_OUGR_USER_ADDRESS;
/
Highly appreciate your help in this regard.
Thanks in advance.
With Best Regards,
Raj