vitaminJava
Reputation: 209
Adding Bulk Collections into procedure in PLSQL
I have a procedure and it is used for inserting a table. It takes another table's datas and join them together then inserting new table. In this procedure I want to use bulk collections for making these operations more faster. I am challenging in syntax and where to use these bulk collections. Does anybody help or share ideas with me? I am adding my database scripts below, thank you from now.
CREATE OR REPLACE procedure FCBSADM.extract_monthly_data(pid_billdate DATE) is
v_cnt NUMBER;
begin
execute immediate 'truncate table gpu';
v_cnt := 0;
FOR REC in (SELECT DISTINCT intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab
WHERE abr.cdate > SYSDATE - 5
AND abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
AND abr.acct_bill_id = ab.acct_bill_id
AND ab.bill_date = pid_billdate)
loop
INSERT INTO GPU
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba2.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
bill_acct ba2,
prod_cmpg pg
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND abr.cdate > SYSDATE - 5
AND ABR.intl_prod_id = rec.intl_prod_id
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id(+);
v_cnt := v_cnt + 1;
IF MOD (v_cnt, 1000) = 0
THEN
COMMIT;
END IF;
COMMIT;
end loop;
COMMIT;
end;
/
This is the last version of it. Errors are gone but no record is coming to GPU table and I am working on it.
create or replace procedure extract_monthly_data(pid_billdate DATE) is
v_cnt NUMBER;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;
l_prod_ids prod_ids_t;
begin
execute immediate 'truncate table gpu';
v_cnt := 0;
SELECT DISTINCT abr.intl_prod_id, ab.bill_date BULK COLLECT INTO l_prod_ids
FROM apld_bill_rt abr,
acct_bill ab
WHERE abr.cdate > SYSDATE - 1000
AND abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
AND abr.acct_bill_id = ab.acct_bill_id
ORDER BY bill_date;
FOR indx IN 1 .. l_prod_ids.COUNT
loop
INSERT INTO GPU
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba2.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr
JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
JOIN FCBSADM.PROD_DST pds ON
JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id
JOIN bill_acct ba2 ON ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
LEFT OUTER JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
WHERE ABR.CHRG_TP = 'INSTALLMENT'
AND abr.cdate > SYSDATE - 5
AND ABR.intl_prod_id =ANY (
SELECT intl_prod_id
FROM apld_bill_rt abr
JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
WHERE abr.cdate > SYSDATE - 1000
AND abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
);
v_cnt := v_cnt + 1;
IF MOD (v_cnt, 1000) = 0
THEN
COMMIT;
END IF;
end loop;
COMMIT;
end;
This is the table that I want to insert into. The procedure compiled successfully but doesnt insert any record.
create table GPU
(
acct_bill_id NUMBER(16),
bill_no VARCHAR2(30 CHAR),
inv_id VARCHAR2(20 CHAR),
bill_date DATE,
bill_acct_id NUMBER(38),
parent_bill_acct_id NUMBER(38),
due_date DATE,
cmpg_id NUMBER,
net_amt NUMBER(16,2),
due_amt NUMBER(16,2),
prod_num VARCHAR2(32 CHAR),
dst_id NUMBER(22) not null,
descr VARCHAR2(100 CHAR),
intl_prod_id NUMBER(14) not null
);
Upvotes: 0
Views: 140
Answers (1)
Roberto Hernandez
Reputation: 8518
You would not need any loop or bulk collect, but it is up to you. The reason is that you have all the values in the select distinct , and you are joining this dataset against the main query. It would be much better use a simple insert inside the procedure.
By the way, you should try to avoid this old syntax and start using normal SQL ANSI which is much easier to understand and maintain.
Option 1
Without cursor, nor bulk collect, just a simple insert select
CREATE OR REPLACE procedure FCBSADM.extract_monthly_data( pid_billdate DATE) is
begin
execute immediate 'truncate table gpu';
-- direct path is faster here, as you are truncating the table before
INSERT /*+append */ INTO GPU
with x as ( SELECT DISTINCT intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab
WHERE abr.cdate > SYSDATE - 5
AND abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
AND abr.acct_bill_id = ab.acct_bill_id
AND ab.bill_date = pid_billdate
)
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba2.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
bill_acct ba2,
prod_cmpg pg,
x
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND abr.cdate > SYSDATE - 5
AND ABR.intl_prod_id = x.intl_prod_id
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id(+);
COMMIT;
end;
Option 2
If you want to use bulk collect, which I don't recommend it here, one option might be. Check for any syntax error, as I did not have any way to replicate the code
CREATE OR REPLACE procedure FCBSADM.extract_monthly_data(pid_billdate DATE) is
v_cnt NUMBER;
TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER; -- whatever the table is
l_prod_ids prod_ids_t;
begin
execute immediate 'truncate table gpu';
v_cnt := 0;
SELECT DISTINCT intl_prod_id BULK COLLECT INTO l_prod_ids
FROM apld_bill_rt abr,
acct_bill ab
WHERE abr.cdate > SYSDATE - 5
AND abr.CHRG_TP = 'INSTALLMENT'
AND abr.TAX_CATG_ID = 'NOTAX'
AND abr.acct_bill_id = ab.acct_bill_id
AND ab.bill_date = pid_billdate);
FOR indx IN 1 .. l_prod_ids.COUNT
loop
INSERT INTO GPU
SELECT AB.ACCT_BILL_ID,
AB.BILL_NO,
AB.INV_ID,
AB.BILL_DATE,
ba2.bill_acct_id,
ba1.bill_acct_id parent_bill_acct_id,
AB.DUE_DATE,
PG.CMPG_ID,
ABR.NET_AMT,
AB.DUE_AMT,
P.PROD_NUM,
pds.DST_ID,
ABR.DESCR,
p.intl_prod_id
FROM apld_bill_rt abr,
acct_bill ab,
prod p,
FCBSADM.PROD_DST pds,
bill_acct_prod bap,
bill_acct ba1,
bill_acct ba2,
prod_cmpg pg
WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
AND bap.intl_prod_id = abr.intl_prod_id
AND ABR.CHRG_TP = 'INSTALLMENT'
AND abr.cdate > SYSDATE - 5
AND ABR.intl_prod_id = l_prod_ids(indx)
AND bap.intl_prod_id = pds.intl_prod_id
AND bap.intl_prod_id = p.intl_prod_id
AND p.intl_prod_id = pg.intl_prod_id(+);
v_cnt := v_cnt + 1;
IF MOD (v_cnt, 1000) = 0
THEN
COMMIT;
END IF;
end loop;
COMMIT;
end;
Upvotes: 2
Related Questions
- Insert collection data into a table using a procedure (kindly read the question for detailed explanation)
- Oracle bulk collect into
- Refactor Oracle stored procedure to use BULK COLLECT
- bulk collect dynamic sql
- Oracle Bulk Collect into Collection using LOOP
- PLSQL collection with bulk collect
- PL/SQL: Getting Error ORA-006550 PLS-00306 in stored procedure
- PLSQL DML statements inside procedure
- Oracle PLSQL BULK Collect and For Loop
- Dealing with PL/SQL Collections