| Re: ORA-02291 [message #18619] |
Thu, 07 February 2002 09:18 |
Jade
Messages: 1
Registered: February 2002
|
Junior Member |
|
|
I have the same problem like you have. Would someone please take a look at the following. Thanks in advance!
*************************************
BEGIN --L3ADDRES
v_table_name := 'L3ADDRES';
-- generate the next sequence number for creating new record in L3ADDRES table
SELECT L3ADDRES_TEMP_SEQ.NEXTVAL INTO v_address_nbr from dual;
INSERT INTO L3ADDRES (SOURCE_SEQ_NBR,L1_ADDRESS_NBR,L1_HSE_NBR_START,
L1_HSE_FRAC_NBR_START,L1_UNIT_START,L1_UNIT_TYPE,L1_STR_DIR,
L1_STR_NAME,L1_STR_SUFFIX,L1_SITUS_CITY,L1_SITUS_COUNTY,L1_SITUS_STATE,
L1_SITUS_ZIP,L1_SITUS_COUNTRY,L1_X_COORD,L1_Y_COORD,L1_ADDR_DESC,
L1_PRIMARY_ADDR_FLG,L1_ADDR_STATUS,REC_DATE,REC_FUL_NAM,REC_STATUS)
VALUES (v_source_seq_nbr,v_address_nbr,input_rec.L1_HSE_NBR_START,
input_rec.L1_HSE_FRAC_NBR_START,input_rec.L1_UNIT_START,input_rec.L1_UNIT_TYPE,
input_rec.L1_STR_DIR,input_rec.L1_STR_NAME,input_rec.L1_STR_SUFFIX,input_rec.L1_SITUS_CITY,
input_rec.L1_SITUS_COUNTY,input_rec.L1_SITUS_STATE,input_rec.L1_SITUS_ZIP,
input_rec.L1_SITUS_COUNTRY,input_rec.L1_X_COORD,input_rec.L1_Y_COORD,
input_rec.L1_ADDR_DESC,'Y','A',SYSDATE,'APOCONV','A');
v_commit := v_commit+1;
EXCEPTION
WHEN dup_val_on_index THEN
BEGIN
SELECT L1_ADDRESS_NBR into v_address_nbr
FROM L3ADDRES
WHERE SOURCE_SEQ_NBR = v_source_seq_nbr
AND NVL(L1_HSE_NBR_START, 0) = NVL(input_rec.L1_HSE_NBR_START, 0)
AND NVL(L1_HSE_FRAC_NBR_START, 'NULL') = NVL(input_rec.L1_HSE_FRAC_NBR_START, 'NULL')
AND NVL(L1_UNIT_START, 'NULL') = NVL(input_rec.L1_UNIT_START, 'NULL')
AND NVL(L1_STR_DIR, 'NULL') = NVL(input_rec.L1_STR_DIR, 'NULL')
AND NVL(L1_STR_NAME, 'NULL') = NVL(input_rec.L1_STR_NAME, 'NULL')
AND NVL(L1_STR_SUFFIX, 'NULL') = NVL(input_rec.L1_STR_SUFFIX, 'NULL')
AND NVL(L1_SITUS_CITY, 'NULL') = NVL(input_rec.L1_SITUS_CITY, 'NULL')
AND NVL(L1_SITUS_ZIP, 'NULL') = NVL(input_rec.L1_SITUS_ZIP, 'NULL');
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_err_msg := input_rec.L1_PARCEL_NBR ||' :no data found from l3addres';
-- write the record to the control table to track the duplicate/bad data
INSERT INTO CONTROL_APO_AGENCY(SOURCE_SEQ_NBR, table_name,
rec_date, err_msg, UDF1,UDF2, UDF3 )
VALUES(v_source_seq_nbr, v_table_name, sysdate, v_err_msg, v_commit,
v_address_nbr, v_owner_nbr );
WHEN TOO_MANY_ROWS THEN
v_err_msg := input_rec.L1_PARCEL_NBR ||' :too many data found from l3addres';
-- write the record to the control table to track the duplicate/bad data
INSERT INTO CONTROL_APO_AGENCY(SOURCE_SEQ_NBR, table_name,
rec_date, err_msg, UDF1,UDF2, UDF3 )
VALUES(v_source_seq_nbr, v_table_name, sysdate, v_err_msg, v_commit,
v_address_nbr, v_owner_nbr );
END; -- WHEN dup_val_on_index L3Addres
WHEN OTHERS THEN
v_err_msg := input_rec.L1_PARCEL_NBR ||' :something wrong with L3Addres';
-- write the record to the control table to track the duplicate/bad data
INSERT INTO CONTROL_APO_AGENCY(SOURCE_SEQ_NBR, table_name,
rec_date, err_msg, UDF1,UDF2, UDF3 )
VALUES(v_source_seq_nbr, v_table_name, sysdate, v_err_msg, v_commit,
v_address_nbr, v_owner_nbr );
END; --L3ADDRES
BEGIN --XPARADDR
v_table_name := 'XPARADDR';
--DBMS_OUTPUT.PUT_LINE('XPARADDR - ' || v_address_nbr || ' - ' || v_source_seq_nbr );
-- insert new record into the cross reference table, XPARADDR
INSERT INTO XPARADDR (SOURCE_SEQ_NBR, L1_ADDRESS_NBR, L1_PARCEL_NBR,
REC_DATE, REC_FUL_NAM, REC_STATUS)
VALUES (v_source_seq_nbr, v_address_nbr, input_rec.L1_PARCEL_NBR, SYSDATE, 'APOCONV', 'A');
v_commit := v_commit+1;
EXCEPTION
WHEN dup_val_on_index THEN
BEGIN
v_err_msg := input_rec.L1_PARCEL_NBR ||' :duplicate xparaddr';
-- write the record to the control table to track the duplicate/bad data
INSERT INTO CONTROL_APO_AGENCY(SOURCE_SEQ_NBR, table_name,
rec_date, err_msg, UDF1,UDF2, UDF3 )
VALUES(v_source_seq_nbr, v_table_name, sysdate, v_err_msg, v_commit,
v_address_nbr, v_owner_nbr );
END;
END; --XPARADDR
************************************
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
