Thread: Fetching datas from two cursors

Good evening every one
problem:
1, Am having two cursors a, b
2, 'a' have fields{a1,a2,a3,a4,....}
3, 'b' have fields{b1}
4, Now i want to fetch data's in the following sequence
r1 ==>val(a1),val(b1),val(a2),val(a3),val(a4),....
r2 ==>val(a1),val(b1),val(a2),val(a3),val(a4),....
etc,..

In PL/SQL Is this possible If possible how
Please Urgent need

Declare r1 as a structure (or record) of a1,a2,a3,a4,b1

fetch from a into r1.a1,r1.a2,r1.a3,r1.a4;
fetch from b into r1.b1;

U should use 2 fetch

But sir here r1 and r2 are rows, similarly there are more than 100 rows in database, i want only specified rows

Hi Kumaran,

Can u give ur problem in a more detailed manner. I couldnt get the exact scenario.

*** Innila ***

i am not clear gvvskumaran.
Are you telling r1 is the row from which u like to fetch into 2 cursors.
or r1 is the structure(or something else) into which u like to fetch the cursor values?

First of all thanks to you friend for replying for me .....

1) Actually i want to fetch multiple rows from two cursors.
2) from first cursor i want to fetch 9 fields
3) from second cursor i want to fetch only one fields
4) I did it using two for loops but duplicate value arise for my pl/sql...

Originally Posted by gvvskumaran

First of all thanks to you friend for replying for me .....

1) Actually i want to fetch multiple rows from two cursors.
2) from first cursor i want to fetch 9 fields
3) from second cursor i want to fetch only one fields
4) I did it using two for loops but duplicate value arise for my pl/sql...

And if it is not clear please wait for some time i will give you the whole scenario

Try to covert them to a single cursor. That would be effective solution.


4) I did it using two for loops but duplicate value arise for my pl/sql...
if you go by for loop...it does not require two for loops. Single loop statement would serve better for u.

loop
/*your logic*/
exit when cursor1%notfound or cursor2%notfound;
end loop;

hope this helps u.

U can try this method.. Set serveroutput on
declare
cursor dept_cursor is
select department_id,department_name
from departments
where department_id < 100
order by department_id;
cursor emp_cursor(v_deptno number) is
select last_name,job_id,hire_date,salary
from employees
where department_id = v_deptno
and employee_id < 120;
v_current_deptno departments.department_id%type;
v_current_dname departments.department_name%type;
v_ename employees.last_name%type;
v_job employees.job_id%type;
v_hiredate employees.hire_date%type;
v_sal employees.salary%type;
v_line varchar2(100);
begin
v_line := '
';
open dept_cursor;
loop
fetch dept_cursor into v_current_deptno,v_current_dname;
exit when dept_cursor%notfound;
dbms_output.put_line ('department number : ' ||
v_current_deptno || ' department name : ' || v_current_dname); dbms_output.put_line(v_line);
if emp_cursor%isopen then
close emp_cursor;
end if;
open emp_cursor (v_current_deptno);
loop
fetch emp_cursor into
v_ename,v_job,v_hiredate,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line (v_ename || ' ' || v_job || ' '
|| v_hiredate || ' ' || v_sal);
end loop;
if emp_cursor%isopen then
close emp_cursor;
end if;
dbms_output.put_line(v_line);
end loop;
if emp_cursor%isopen then
close emp_cursor;
end if;
close dept_cursor;
end;
/
set serveroutput off