Results 1 to 9 of 9

Thread: Fetching datas from two cursors

  1. #1
    Junior Member
    Join Date
    Jan 2007
    Answers
    16

    Cool 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


  2. #2
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Fetching datas from two cursors

    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


  3. #3
    Junior Member
    Join Date
    Jan 2007
    Answers
    16

    Re: Fetching datas from two cursors

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


  4. #4
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Fetching datas from two cursors

    Hi Kumaran,

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

    *** Innila ***


  5. #5
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Fetching datas from two cursors

    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?


  6. #6
    Junior Member
    Join Date
    Jan 2007
    Answers
    16

    Re: Fetching datas from two cursors

    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...


  7. #7
    Junior Member
    Join Date
    Jan 2007
    Answers
    16

    Re: Fetching datas from two cursors

    Quote Originally Posted by gvvskumaran View Post
    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


  8. #8
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Fetching datas from two cursors

    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.


  9. #9
    Junior Member
    Join Date
    Oct 2009
    Answers
    3

    Re: Fetching datas from two cursors

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact