Cursor For Loop Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Cursor For Loop Question

  1. #1
    Join Date
    Nov 2002
    Location
    NYC
    Posts
    9

    Cursor For Loop Question

    Hi,
    I have two tables one DEPT and employee tables. I am trying to return resultset (using ref cursor) like
    for every 1 row of dept the corresponding employeess
    for eg for dept "SALES"
    select * from Dept where type = "sales"
    select * from employess where type = "sales"

    for that i declare a package with ref cursor
    and procedure is as follows
    procedure LoadDeptEmployess ( generic_cursor in out instr_ref_cursor )
    is
    cursor deptCk is select deptId from Dept;
    begin
    /* so far each row in dept fetch all the employees */
    for dept_rec in deptCk Loop
    open generic_cursor for select * from Dept
    where deptd = dept_rec.deptId;
    end loop;
    end;

    end package;

    when i tried to execute it i am only seeing the last rows in dept table. i don't know why?
    if anybody knows the answer or suggestion to get all the rows will be really helpful.

    thanks
    vijay

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    I am missing some code, but it seems that:

    for dept_rec in deptCk Loop
    open generic_cursor for select * from Dept
    where deptd = dept_rec.deptId;
    end loop;

    loops complete through dept_rec and then exits the procedure
    and that is at the last record.
    Regards
    Ben de Boer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width