Collection Examples

CAST Functions to fetch values from the Oracle Nested Table

drop  type emp_obj;--Droping Objects

CREATE TYPE emp_obj AS OBJECT (empno    number(4,0),

  ename    varchar2(10), 

   job      varchar2(9),  

   mgr      number(4,0), 

    hiredate date,  sal      number(7,2),  

    comm     number(7,2),  

    deptno   number(2,0));

CREATE TYPE emp_tbl_typ AS TABLE OF emp_obj;

select * from table(cast( null as emp_tbl_typ ))

DECLARE

emp_tbl     emp_tbl_typ := emp_tbl_typ();

v_emp_obj     emp_obj;

cursor c1 is select ename,sal from emp;

BEGIN

  for i in c1 

     loop    

       emp_tbl.extend;

       v_emp_obj :=  emp_obj(null,i.ename,null,Null,null,i.sal,null,Null);

       emp_tbl(emp_tbl.last):=  v_emp_obj;

  end loop;

  for i in 1..emp_tbl.last loop

    dbms_output.put_line('==>'||emp_tbl(i).ename||'=='||emp_tbl(i).sal);

    end loop;

    

for i in (select * from table(cast(emp_tbl as emp_tbl_typ ))) --Casting Nested table 

loop

  dbms_output.put_line('==>'||i.ename||'=='||i.sal);

  end loop; 

END;

-- while loop for Iteration between PLS table
declare
type emp_rec is record(empno number,empname varchar2(60));
type emp_tab_ty is table of emp_rec index by pls_integer;
emp_tab emp_tab_ty;
val_empno number(10);
var_name  varchar2(60);
varemp_rec emp_rec;
begin
  for i in (select empno,ename from emp order by empno)
  loop 
    emp_tab(i.empno).empno :=  i.empno;
    emp_tab(i.empno).empname := i.ename; 
   dbms_output.put_line('Index=>'||emp_tab(i.empno).empname||'--empno--'||emp_tab(i.empno).empno||'-first-'||emp_tab.first||'-count-'||emp_tab.count);
    end loop;
    ---<while loop>
     val_empno := null;
     val_empno := emp_tab.first;
while val_empno  IS NOT NULL 
  LOOP
  dbms_output.put_line('while loop value==>'||emp_tab.next(val_empno)||'--'||val_empno||'--'||emp_tab(val_empno).empno||'--'||emp_tab(val_empno).empname);
  
  varemp_rec.empname   := emp_tab(val_empno).empname ;

  IF  emp_tab(val_empno).empname = 'ABC' THEN
        dbms_output.put_line('This is Sparta-1');
  End if;                 
  
  val_empno := emp_tab.next(val_empno);
  
  IF varemp_rec.empname = 'ABC' THEN
        dbms_output.put_line('No This is Sparta');
 End if;                 
  
  end loop;
  exception when others then
     dbms_output.put_line('Exception'||Sqlerrm ||'--'||sqlcode);
  end;
---
Function Returns Nested table for basic understanding

1. CREATE OR REPLACE TYPE t_tf_row AS OBJECT (id   NUMBER                      ,descriptionVARCHAR2(50)) --Creating the table 

2. CREATE OR REPLACE TYPE t_tf_tab IS TABLE OF t_tf_row

3.CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER)
     RETURN t_tf_tab AS --Returning Nested table
    l_tab  t_tf_tab := t_tf_tab();
BEGIN
  FOR i IN (select empno,ename from emp) LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tf_row(i.empno,i.ename);
  END LOOP;

  RETURN l_tab;
END;

4.select get_tab_tf (10) from dual;
------------------------------------------------------------------------------------------------
Function Returns Nested table for basic understanding with reference cursor,and calling Reference cursor in select statement

1.   CREATE OR REPLACE TYPE t_tf_row AS OBJECT
                                                                          (id   NUMBER,
                                                                           description  VARCHAR2(50)); --Creating Object

2. CREATE OR REPLACE TYPE t_tf_tab IS TABLE OF t_tf_row; --Creating Table

3.CREATE OR REPLACE FUNCTION get_tab_tf1 (p_rows IN sys_refcursor)
                                                               RETURN t_tf_tab AS
  l_tab  t_tf_tab := t_tf_tab();
  l_fetch varchar2(10);
  l_fetch_name varchar2(60);
BEGIN
   LOOP
   Fetch p_rows into l_fetch,l_fetch_name;
    l_tab.extend;
   l_tab(l_tab.last) := t_tf_row(l_fetch,l_fetch_name);
   exit when p_rows%notfound;
  END LOOP;

  RETURN l_tab;
END;

4. select get_tab_tf1(cursor(select sal,ename from emp)) employee from dual;
--