drop type sql_emp_tab_type ;

drop type  sql_emp_type ;

create or replace type sql_emp_type as object
(
  empno number,
  ename varchar2(20),
  sal   number , 
  deptno number
)
;
create or replace type sql_emp_tab_type as table of sql_emp_type ;

SQL> 
SQL> declare
  2  
  3    tab_type_var sql_emp_tab_type := sql_emp_tab_type();
  4  
  5    begin
  6  
  7    tab_type_var.extend(10);
  8  
  9    select se.empno, se.ename, se.sal, se.deptno bulk collect
 10      into tab_type_var
 11      from scott.emp se
 12     where se.deptno = 10;
 13  
 14    dbms_output.put_line('tab_type_var ' || tab_type_var.count);
 15  
 16  end;
 17  /
 
declare

  tab_type_var sql_emp_tab_type := sql_emp_tab_type();

  begin

  tab_type_var.extend(10);

  select se.empno, se.ename, se.sal, se.deptno bulk collect
    into tab_type_var
    from scott.emp se
   where se.deptno = 10;

  dbms_output.put_line('tab_type_var ' || tab_type_var.count);

end;
 
ORA-06550: line 12, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored

SQL> declare
  2  
  3  --tab_type_var   sql_emp_tab_type  := sql_emp_tab_type();
  4  
  5  type pl_sql_emp_type is record
  6  (
  7    empno number,
  8    ename varchar2(20),
  9    sal   number ,
 10    deptno number) ;
 11  
 12  type pl_sql_tab_type is table of  pl_sql_emp_type ;
 13  
 14  tab_type_var  pl_sql_tab_type ;
 15  
 16  begin
 17  
 18  --tab_type_var.extend(10);
 19  
 20  select se.empno, se.ename, se.sal, se.deptno bulk collect
 21    into tab_type_var
 22    from scott.emp se
 23   where se.deptno = 10;
 24  
 25   dbms_output.put_line( 'tab_type_var '||tab_type_var.count);
 26  
 27  end  ;
 28  /
 
tab_type_var 3
 
PL/SQL procedure successfully completed
 
SQL> 

SQL> declare
  2  
  3    tab_type_var sql_emp_tab_type := sql_emp_tab_type();
  4  
  5    begin
  6  
  7    select sql_emp_type (se.empno, se.ename, se.sal, se.deptno)
  8      bulk collect into tab_type_var
  9      from scott.emp se
 10     where se.deptno = 10;
 11  
 12    dbms_output.put_line('tab_type_var ' || tab_type_var.count);
 13  
 14  end;
 15  /
tab_type_var 3

PL/SQL procedure successfully completed.

SQL> declare
  2  
  3  tab_type_var   sql_emp_tab_type  := sql_emp_tab_type();
  4  rec_1  sql_emp_type :=  sql_emp_type(123, 'XYZ',10.23 , 20);
  5  rec_2  sql_emp_type :=  sql_emp_type(852, 'abc',18569 , 10);
  6  rec_3  sql_emp_type :=  sql_emp_type(987, 'mno',9521 , 20);
  7  
  8  
  9  begin
 10  tab_type_var.delete();
 11  tab_type_var.extend(10);
 12  tab_type_var(1)  :=  rec_1 ;
 13  tab_type_var(2)  :=  rec_2 ;
 14  tab_type_var(3)  :=  rec_3 ;
 15  
 16  for i in tab_type_var.first .. tab_type_var.last loop
 17  dbms_output.put_line( 'tab_type_var '||tab_type_var(i).ename);
 18  end loop;
 19  
 20  end  ;
 21  /
 
tab_type_var XYZ
tab_type_var abc
tab_type_var mno
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
tab_type_var 
 
PL/SQL procedure successfully completed
 
SQL>