Re: Fetching into arrays

Hi Andreas,

Maybe this helps: use type declaration and populate your array with a cursor.

Example (to be wrapped in a package):

• ---------------------------------------------------------
• in the pkg spec

  TYPE t_emp_dept_rec IS RECORD (empno emp.empno%type,     ename emp.ename%type,
    ...
    deptno dept.deptno%type,
    dname dept.dname%type);

  TYPE t_emp_dept_tbl IS TABLE OF t_emp_dept_rec INDEX BY BINARY_INTEGER;

• in the body

  function f_fetch_into_array return t_emp_dept_tbl is

    the_array t_emp_dept_tbl;

    i number := 0;

    cursor c_emp_dept is

      select e.empno,
      e.ename,
        ...
     d.deptno,
     d.dname
      from emp e, dept d
      where d.deptno = e.deptno;

  begin

    for rc_emp_dept in c_emp_dept loop

      the_array(i).empno := rc_emp_dept.empno;
      the_array(i).ename := rc_emp_dept.ename;
      ...
      the_array(i).deptno := rc_emp_dept.deptno;
      the_array(i).dname := rc_emp_dept.dname;
      i:= i+1;

    return the_array;

  end;$

• --------------------------------------------------------- ...hope this helps

Cheers

--

Nicolas Stern
Paul Scherrer Institut Received on Mon Aug 27 2001 - 09:58:58 CEST