Hi Sir,
I hope you are doing well and thank you for providing oracle support, Really appreciate for your efforts.
Currently I am facing one issue in production so cannot copy the code here but i have written below for reference.
I am having 1 procedure in which data insert is processing for multiple tables, When I call the prc 1 record is inserted in tables there are multiple tables with some condition this process for single call runs faster or in seconds we can say. But I want to call it 1 lakh time as i have to create 1 lakh records for some testing.
I am calling the prc using loop but it is very slow as taking 20 mins for creating only 2k records. Is there any alternative for calling prc ?
As below is logic but in actual there are lots of validation block and multiple insertion
Note: for single call it runs in sec but when calling in loop for 2k times it takes apprx 20 mins and in tables there are no triggers
-------------------------------------------------------------------------------------------------
CREATE TABLE BLK_EMP
(
EMP_BATCH VARCHAR2(10 BYTE),
EMP_ID NUMBER,
E_RECID NUMBER,
DEPTNO NUMBER
);
/
create or replace TYPE e_address_obj AS OBJECT ( address VARCHAR2(50),
pincode NUMBER);
/
create or replace TYPE emp_addr_tab as table of e_address_obj;
/
create table t_rec
as
select (9876234+level) as eid
from dual
connect by level <= 300;
/
create sequence seq_emp_id;
/
--prc
create or replace package pkg_emp_addr
as
procedure prc_emp_addr(
in_batch_emp IN varchar
,in_empno IN NUMBER
,in_empaddr IN emp_addr_tab
,in_e_recid IN NUMBER
,in_out_deptno IN OUT NUMBER
);
end pkg_emp_addr;
/
create or replace PACKAGE BODY PKG_EMP_ADDR AS
procedure prc_emp_addr(
in_batch_emp IN varchar
,in_empno IN NUMBER
,in_empaddr IN emp_addr_tab
,in_e_recid IN NUMBER
,in_out_deptno IN OUT NUMBER
)
AS
L_DEPTNO NUMBER;
l_seq_emp_id number;
--l_table emp_addr_tab := emp_addr_tab();
BEGIN
L_DEPTNO := in_out_deptno;
IF L_DEPTNO IS NOT NULL
THEN
SELECT DEPTNO INTO in_out_deptno
FROM EMP
WHERE EMPNO = 7499;
ELSE
in_out_deptno := 101;
END IF;
l_seq_emp_id := seq_emp_id.nextval;
insert into blk_emp values(in_batch_emp, l_seq_emp_id, in_e_recid, in_out_deptno);
--commit;
exception
when others then
rollback;
raise_application_error(-20001,sqlerrm);
END prc_emp_addr;
END PKG_EMP_ADDR;
/
---calling it
declare
in_batch_emp varchar(2);
in_empno NUMBER;
in_out_deptno NUMBER;
TYPE myarray IS TABLE OF number;
l_data myarray;
emp_add_t emp_addr_tab;
CURSOR r IS
SELECT eid
FROM t_rec;
begin
emp_add_t := emp_addr_tab();
emp_add_t.extend(3);
emp_add_t(1) := e_address_obj('usa',440022);
emp_add_t(2) := e_address_obj('ind',401122);
emp_add_t(3) := e_address_obj('germany',662200);
for a in 1..1000
loop
open r;
loop
fetch r bulk collect into l_data limit 100;
for i in 1..l_data.count
loop
for idx IN emp_add_t.FIRST .. emp_add_t.COUNT
LOOP
in_out_deptno := null;
PKG_EMP_ADDR.prc_emp_addr(
'c'
,0
,emp_add_t
,l_data(i)
,in_out_deptno
);
END LOOP;
end loop;
exit when r%notfound;
end loop;
close r;
end loop;
commit;
exception
when others then
raise_application_error(-20001,sqlerrm);
end;
/
array is also there but not using in above procedure just use for calling reference
--added the object type
You are doing a whole stack of single row operations.... you want to look at doing things in sets. The database works (best) with sets not with rows.
For exmaple, you might do all your emp_batch inserts in a single operation after the for-idx loop
SQL>
SQL> create or replace package pkg_emp_addr
2 as
3 type batch_list is table of blk_emp%rowtype
4 index by pls_integer;
5
6 g_batch_list batch_list;
7
8 procedure prc_emp_addr(
9 in_batch_emp IN varchar
10 ,in_empno IN NUMBER
11 ,in_empaddr IN emp_addr_tab
12 ,in_e_recid IN NUMBER
13 ,in_out_deptno IN OUT NUMBER
14 );
15
16 procedure batch_insert;
17 end pkg_emp_addr;
18 /
Package created.
SQL>
SQL> create or replace PACKAGE BODY PKG_EMP_ADDR AS
2 g_default_deptno int;
3
4 procedure prc_emp_addr(
5 in_batch_emp IN varchar
6 ,in_empno IN NUMBER
7 ,in_empaddr IN emp_addr_tab
8 ,in_e_recid IN NUMBER
9 ,in_out_deptno IN OUT NUMBER
10 )
11 AS
12
13 L_DEPTNO NUMBER;
14 l_seq_emp_id number;
15 --l_table emp_addr_tab := emp_addr_tab();
16 BEGIN
17
18 L_DEPTNO := in_out_deptno;
19
20 IF L_DEPTNO IS NOT NULL
21 THEN
22 in_out_deptno := g_default_deptno;
23 ELSE
24 in_out_deptno := 101;
25 END IF;
26
27
28 l_seq_emp_id := seq_emp_id.nextval;
29 g_batch_list(g_batch_list.count+1).EMP_BATCH := in_batch_emp;
30 g_batch_list(g_batch_list.count).EMP_ID := l_seq_emp_id;
31 g_batch_list(g_batch_list.count).E_RECID := in_e_recid;
32 g_batch_list(g_batch_list.count).DEPTNO:= in_out_deptno;
33
34 exception
35 when others then
36 rollback;
37 raise_application_error(-20001,sqlerrm);
38
39 END prc_emp_addr;
40
41 procedure batch_insert is
42 begin
43 forall i in 1 .. g_batch_list.count
44 insert into blk_emp values g_batch_list(i);
45 end;
46
47 begin
48 SELECT DEPTNO INTO g_default_deptno
49 FROM EMP
50 WHERE EMPNO = 7499;
51 END PKG_EMP_ADDR;
52 /
Package body created.
SQL>
SQL> ---calling it
SQL>
SQL> set serverout on
SQL> declare
2 in_batch_emp varchar(2);
3 in_empno NUMBER;
4 in_out_deptno NUMBER;
5 TYPE myarray IS TABLE OF number;
6 l_data myarray;
7 emp_add_t emp_addr_tab;
8 CURSOR r IS
9 SELECT eid
10 FROM t_rec;
11 begin
12
13 emp_add_t := emp_addr_tab();
14 emp_add_t.extend(3);
15 emp_add_t(1) := e_address_obj('usa',440022);
16 emp_add_t(2) := e_address_obj('ind',401122);
17 emp_add_t(3) := e_address_obj('germany',662200);
18
19 for a in 1..100 -- 1000
20 loop
21
22 open r;
23 loop
24 fetch r bulk collect into l_data limit 100;
25 for i in 1..l_data.count
26 loop
27 pkg_emp_addr.g_batch_list.delete;
28
29 for idx IN emp_add_t.FIRST .. emp_add_t.COUNT
30 LOOP
31 in_out_deptno := null;
32 PKG_EMP_ADDR.prc_emp_addr(
33 'c'
34 ,0
35 ,emp_add_t
36 ,l_data(i)
37 ,in_out_deptno
38 );
39 END LOOP;
40 PKG_EMP_ADDR.batch_insert;
41
42
43 end loop;
44 exit when r%notfound;
45 end loop;
46 close r;
47 end loop;
48
49 commit;
50 exception
51 when others then
52 raise_application_error(-20001,sqlerrm);
53 end;
54 /
PL/SQL procedure successfully completed.
SQL>
SQL>