A Simple Example of Weak Ref Cursor

DECLARE
  l_sql   VARCHAR2(2000) := 'select deptno,loc  scott.dept';
  l_where VARCHAR2(200);

  TYPE dept_curtype IS REF CURSOR;
  dept_cur dept_curtype;
  TYPE dept_rec IS RECORD(
     deptno VARCHAR2(200) --attention the record element order
    ,loc    VARCHAR2(200));
  TYPE dept_tbl IS TABLE OF dept_rec;
  l_dept_tbl dept_tbl;

BEGIN
  l_where := ' where deptno = :deptno';
  l_sql   := l_sql || l_where;
  OPEN dept_cur FOR l_sql
    USING 40 --USING is not necessary
  ;
  FETCH dept_cur BULK COLLECT
    INTO l_dept_tbl;
  CLOSE dept_cur;

  dbms_output.put_line('l_str_tbl.COUNT = ' || l_dept_tbl.COUNT);
<<l_dept_tbl_loop>>
  FOR i IN 1 .. l_dept_tbl.COUNT
  LOOP
    dbms_output.put_line('l_str_tbl(' || i || ').deptno = ' ||
                         l_dept_tbl(i).deptno || ', l_str_tbl(' || i ||
                         ').loc = ' || l_dept_tbl(i).loc);

  END LOOP l_dept_tbl_loop;
END;