Hello,
I try to use bulk collect to modify an old PL/SQL (runs, but runs slow),
--original one
DECLARE
CURSOR C_CURSOR IS SELECT * FROM MF_S224_RPT WHERE UIDY_LO IS NOT NULL AND PATN_LIST IS NOT NULL;
V_DEF_DATA C_CURSOR % ROWTYPE;
str_new_uidy_lo VARCHAR2(255);
CURSOR C_CURSOR_LN IS SELECT * FROM MF_S224_RPT_LN WHERE UIDY_LO IS NOT NULL;
V_DEF_DATA_LN C_CURSOR_LN % ROWTYPE;
str_new_uidy_lo_ln VARCHAR2(255);
BEGIN
OPEN C_CURSOR;
FETCH C_CURSOR INTO V_DEF_DATA;
WHILE C_CURSOR % FOUND LOOP
str_new_uidy_lo :=('&6084&'||V_DEF_DATA.FISC_YEAR||'&'||V_DEF_DATA.FISC_MNTH||'&'||V_DEF_DATA.VRSN||'&'||V_DEF_DATA.AGCY||'&'||V_DEF_DATA.PATN_LIST||'&'||V_DEF_DATA.ALC);
UPDATE MF_S224_RPT T1 SET T1.UIDY_LO = str_new_uidy_lo WHERE T1.UIDY_LO = V_DEF_DATA.UIDY_LO;
UPDATE MF_S224_RPT_LN T2 SET T2.PRPT_ID_LO = str_new_uidy_lo WHERE T2.PRPT_ID_LO = V_DEF_DATA.UIDY_LO;
UPDATE MF_S224_RPT_ACT T5 SET T5.PRPT_ID_LO = str_new_uidy_lo WHERE T5.PRPT_ID_LO = V_DEF_DATA.UIDY_LO;
FETCH C_CURSOR INTO V_DEF_DATA;
END LOOP;
CLOSE C_CURSOR;
OPEN C_CURSOR_LN;
FETCH C_CURSOR_LN INTO V_DEF_DATA_LN;
WHILE C_CURSOR_LN % FOUND LOOP
str_new_uidy_lo_ln :=('&6085'||SUBSTR(V_DEF_DATA_LN.PRPT_ID_LO, 6)||'&'||V_DEF_DATA_LN.TSYM||'&'||V_DEF_DATA_LN.GLAC||'&'||V_DEF_DATA_LN.ACMP_YEAR||'&'||V_DEF_DATA_LN.ACMP_MNTH||'&'||V_DEF_DATA_LN.RCPT_DISB_IN||'&'||V_DEF_DATA_LN.PATN);
UPDATE MF_S224_RPT_LN T3 SET T3.UIDY_LO = str_new_uidy_lo_ln WHERE T3.UIDY_LO = V_DEF_DATA_LN.UIDY_LO;
UPDATE MF_S224_RPT_ACT T4 SET T4.PRPT_LINE_ID_LO = str_new_uidy_lo_ln WHERE T4.PRPT_LINE_ID_LO = V_DEF_DATA_LN.UIDY_LO;
FETCH C_CURSOR_LN INTO V_DEF_DATA_LN;
END LOOP;
CLOSE C_CURSOR_LN;
END;
/
---Modify, using Bulk Collect
DECLARE
CURSOR C_CURSOR IS SELECT * FROM MF_S224_RPT WHERE UIDY_LO IS NOT NULL AND PATN_LIST IS NOT NULL;
TYPE c_arr1 IS TABLE OF C_CURSOR%ROWTYPE;
c_rows1 c_arr1;
str_new_uidy_lo VARCHAR2(500);
CURSOR C_CURSOR_LN IS SELECT * FROM MF_S224_RPT_LN WHERE UIDY_LO IS NOT NULL;
TYPE c_arr2 IS TABLE OF C_CURSOR_LN%ROWTYPE;
c_rows2 c_arr2;
str_new_uidy_lo_ln VARCHAR2(500);
BEGIN
OPEN C_CURSOR;
FETCH C_CURSOR BULK COLLECT INTO c_rows1 LIMIT 2000;
FORALL i IN c_rows1.FIRST..c_rows1.LAST
str_new_uidy_lo :=('&6084&'||c_rows1(i).FISC_YEAR||'&'||c_rows1(i).FISC_MNTH||'&'||c_rows1(i).VRSN||'&'||c_rows1(i).AGCY||'&'||c_rows1(i).PATN_LIST||'&'||c_rows1(i).ALC);
UPDATE MF_S224_RPT T1 SET T1.UIDY_LO = str_new_uidy_lo WHERE T1.UIDY_LO = c_rows1(i).UIDY_LO;
UPDATE MF_S224_RPT_LN T2 SET T2.PRPT_ID_LO = str_new_uidy_lo WHERE T2.PRPT_ID_LO = c_rows1(i).UIDY_LO;
UPDATE MF_S224_RPT_ACT T5 SET T5.PRPT_ID_LO = str_new_uidy_lo WHERE T5.PRPT_ID_LO = c_rows1(i).UIDY_LO;
EXIT WHEN c_rows1.COUNT < 2000;
END LOOP;
CLOSE C_CURSOR;
OPEN C_CURSOR_LN;
FETCH C_CURSOR_LN BULK COLLECT INTO c_rows2 LIMIT 2000;
FORALL i IN c_rows2.FIRST..c_rows2.LAST
str_new_uidy_lo_ln :=('&6085'||SUBSTR(c_rows2(i).PRPT_ID_LO, 6)||'&'||c_rows2(i).TSYM||'&'||c_rows2(i).GLAC||'&'||c_rows2(i).ACMP_YEAR||'&'||c_rows2(i).ACMP_MNTH||'&'||c_rows2(i).RCPT_DISB_IN||'&'||c_rows2(i).PATN);
UPDATE MF_S224_RPT_LN T3 SET T3.UIDY_LO = str_new_uidy_lo_ln WHERE T3.UIDY_LO = c_rows2(i).UIDY_LO;
UPDATE MF_S224_RPT_ACT T4 SET T4.PRPT_LINE_ID_LO = str_new_uidy_lo_ln WHERE T4.PRPT_LINE_ID_LO = c_rows2(i).UIDY_LO;
EXIT WHEN c_rows2.COUNT < 2000;
END LOOP;
CLOSE C_CURSOR_LN;
END;
/
(while, get error)
Error report:
ORA-06550: line 18, column 3:
PLS-00103: Encountered the symbol "STR_NEW_UIDY_LO" when expecting one of the following:
. ( * @ % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
ORA-06550: line 18, column 172:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between overlaps || multise
ORA-06550: line 24, column 3:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 29, column 7:
PLS-00103: Encountered the symbol "STR_NEW_UIDY_LO_LN" when expecting one of the following:
. ( * @ % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
ORA-06550: line 29, column 221:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between overlaps || multise
ORA-06550: line 34, column 3:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I didn't see anything wrong yet...could you help to take a look?
Thank you very much!!!