Skip to Main Content

collection is giving different row count...

User_3YG1KOct 9 2022

Hi,
I am trying to retrieve the data in different combinations by writing different procedures inside a package. but requirement implemented is same in both procedures .. Just displaying emp_id, emp_name, number of rows retrieved.
in the first procedure (Prc_get_emp_dtls ), its normal retrieval of data. where the count is 9 . for dept_id =2
But.....
in the second procedure (Prc_get_empdtls), it gives the row count as 18 for the same dept_id =2

--- here is the strange thing is when i use table type collection and retrieved the data, the row count is different.

May i know where it is going wrong...

here is the sample data...
create table DEPT
(
dept_id NUMBER not null,
dept_name VARCHAR2(40),
dept_code VARCHAR2(30),
constraint pk_dept primary key
)
insert into dept (DEPT_ID, DEPT_NAME, DEPT_CODE)
values (1, 'Information Technology', 'IT');

insert into dept (DEPT_ID, DEPT_NAME, DEPT_CODE)
values (2, 'Accounts', 'ACNTS');

insert into dept (DEPT_ID, DEPT_NAME, DEPT_CODE)
values (3, 'Life Insurance', 'LIC');

insert into dept (DEPT_ID, DEPT_NAME, DEPT_CODE)
values (4, 'Finance', 'FIN');

insert into dept (DEPT_ID, DEPT_NAME, DEPT_CODE)
values (5, 'Municipal Corporation', 'MUNCICOPR');

insert into dept (DEPT_ID, DEPT_NAME, DEPT_CODE)
values (6, 'Network', 'Ntwrk');

create table EMP
(
emp_id NUMBER not null,
emp_name VARCHAR2(30) not null,
dept_id NUMBER,
constraint Pk_emp primary key,
constraint fk_emp foreign key references dept(dept_id)
);
insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (1, 'aaaa', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (2, 'bbbb', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (5, 'cccc', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (9, 'dddd', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (3, 'eeee', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (13, 'ffff', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (25, 'gggg', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (10, 'hhhh', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (11, 'iiii', 2);

insert into emp (EMP_ID, EMP_NAME, DEPT_ID)
values (12, 'aaaa', 1);
-----> I have created a package with different combinations where each procedure displays emp_id, emp_name, records retrieved count.

------> here is the package
create or replace package Collections_Emp is
Procedure Prc_get_emp_dtls (p_deptid dept.dept_id%Type);
Procedure Prc_get_empdtls(P_deptid emp.dept_id%Type);
end Collections_Emp;

create or replace package body Collections_Emp Is
Procedure Prc_get_emp_dtls (p_deptid dept.dept_id%Type) Is ---0.792 sec
v_empid emp.emp_id%Type;
v_empname emp.emp_name%Type;
Cursor get_data Is
Select emp_id, emp_name From emp Where dept_id = p_deptid; -- dept_id=2
Begin
Open get_data;
Loop
Fetch get_data Into v_empid, v_empname;
Exit When get_data%Notfound;
dbms_output.put_line(v_empid||','||v_empname);
End Loop;
dbms_output.put_line('Rows Fetched :' || get_data%Rowcount);
Close get_data;
End Prc_get_emp_dtls;
Procedure Prc_get_empdtls(P_deptid emp.dept_id%Type) Is -- Executed in 0'758 seconds
Type emp_ty Is Record (t_emp_id emp.emp_id%Type,
t_empname emp.emp_name%Type);
Type empty_tbl Is Table Of emp_ty;
v_empty_tbl empty_tbl:=empty_tbl();
Begin
Select emp_id, emp_name Bulk Collect Into v_empty_tbl From emp Where dept_id= P_deptid;
For i In v_empty_tbl.first..v_empty_tbl.last
Loop
v_empty_tbl.extend;
dbms_output.put_line(v_empty_tbl(i).t_emp_id ||','|| v_empty_tbl(i).t_empname);
End Loop;
dbms_output.put_line('No of rows : ' || v_empty_tbl.count);
End Prc_get_empdtls;
End Collections_Emp;
Please help me on this
Thanks for the timely reply.

This post has been answered by Solomon Yakobson on Oct 9 2022
Jump to Answer
Comments
Post Details
Added on Oct 9 2022
2 comments
46 views