I am a naive to the Oracle collections & Dynamic SQL concepts and still in process of learning, below is a procedure i wrote. First it will delete the records if a version of records already exists and update the dates as null of the previous version records to establish them as the current version. Appreciate if you can pinpoint any mistakes, i am thinking of doing bulk binding when i am doing update but still struggling to come up with an approach, please help me with any inputs. Thank you.
create or replace
procedure fcs_stage_data_delete2(p_extract_dt in varchar2,p_provider in varchar2,p_uninum in varchar2,p_ver_num in number,retval out number)
IS
i_commit pls_integer := 100000;
i_rowcount pls_integer := 0;
v_code NUMBER;
v_errm VARCHAR2(64);
TYPE TABLIST IS TABLE OF VARCHAR2(20);
TABNAME TABLIST := TABLIST('FEED_PROCESS_LOG','CUSTOMER','LOAN','HASH_TOTALS_FY1213');
begin
--------------------------------------------------------------------------------------------------------------------------
/* DELETION OF STAGE TABLES */
--------------------------------------------------------------------------------------------------------------------------
begin
DELETE FROM CUSTOMER_LOAN_XREF
WHERE CUSTOMER_EXTRACT_DT=p_extract_dt
AND CUSTOMER_PROVIDER=P_PROVIDER
AND CUSTOMER_UNINUM=P_UNINUM
AND CUSTOMER_VER_NUM=P_VER_NUM
AND LOAN_EXTRACT_DT=p_extract_dt
AND LOAN_PROVIDER=p_provider
AND LOAN_UNINUM=p_uninum
AND LOAN_VER_NUM=p_ver_num;
COMMIT;
end;
EXECUTE IMMEDIATE 'ALTER TABLE CUSTOMER_LOAN_XREF DISABLE CONSTRAINT FK_CUSTOMER_LOAN_XREF_CUSTOMER';
EXECUTE IMMEDIATE 'ALTER TABLE CUSTOMER_LOAN_XREF DISABLE CONSTRAINT FK_CUSTOMER_LOAN_XREF_LOAN';
FOR I IN TABNAME.FIRST..TABNAME.LAST
LOOP
EXECUTE IMMEDIATE
'DELETE FROM '||TABNAME(I)||'
WHERE EXTRACT_DT=:A
AND PROVIDER=:B
AND UNINUM=:C
AND VER_NUM=:D'
USING P_EXTRACT_DT,P_PROVIDER,P_UNINUM,P_VER_NUM;
COMMIT;
END LOOP;
EXECUTE IMMEDIATE 'ALTER TABLE CUSTOMER_LOAN_XREF ENABLE CONSTRAINT FK_CUSTOMER_LOAN_XREF_CUSTOMER';
EXECUTE IMMEDIATE 'ALTER TABLE CUSTOMER_LOAN_XREF ENABLE CONSTRAINT FK_CUSTOMER_LOAN_XREF_LOAN';
-------------------------------------------------------------------------------------------------------------------------
/* UPDATION OF STAGE TABLES*/
-------------------------------------------------------------------------------------------------------------------------
IF p_ver_num > 1
then
begin
for i in (SELECT ROWID,T.* FROM FEED_PROCESS_LOG T WHERE T.EXTRACT_DT=P_EXTRACT_DT
and T.PROVIDER=P_PROVIDER AND T.UNINUM=P_UNINUM AND T.VER_NUM=P_VER_NUM-1)
loop
UPDATE FEED_PROCESS_LOG SET ACT_END_DT=NULL,UPDATE_ID=NULL,UPDATE_DT=NULL WHERE ROWID=I.ROWID;
end loop;
commit;
end;
begin
for i in (SELECT ROWID,T.* FROM CUSTOMER T WHERE T.EXTRACT_DT=p_extract_dt and T.PROVIDER=P_PROVIDER
AND T.UNINUM=P_UNINUM AND T.VER_NUM=P_VER_NUM-1)
loop
UPDATE CUSTOMER SET ACT_END_DT=NULL WHERE ROWID=i.ROWID;
end loop;
commit;
end;
end if;
retval:=0;
exception when others
then
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 64);
retval:=1;
end fcs_stage_data_delete2;