oracle-databaseplsqlplsql-packagebulk-collect

Unable to print more then one column using bulk collect


I need to print all column data using bulk collect how do i need to achieve it

My emp table as : id , name , salary

This id, name , salary need to be printed as an ouput

Below is my PLSQLcode

declare 

  type v_nest is table of emp%rowtype;
  v_rd v_nest := v_nest();
  
begin

     select * bulk collect into v_rd from emp;
  
     for i in v_rd.first..v_rd.last
     loop
        dbms_output.put_line(v_rd(i));  // Why this does not work 
     end loop; 
     
end;

Solution

  • dbms_output expects a scalar - you can't pass it a record with multiple columns, which is what each element of the v_rd collection is (a record/object of type emp%rowtype). So, simply break it out any way you wish into your own string of choice, by adding the column names to your record references:

    declare 
    
      type v_nest is table of emp%rowtype;
      v_rd v_nest := v_nest();
      
    begin
    
         select * bulk collect into v_rd from emp;
      
         for i in v_rd.first..v_rd.last
         loop
            dbms_output.put_line(v_rd(i).id||','||v_id(i).name||','||v_rd(i).salary);  
         end loop; 
         
    end;
    

    A useful academic exercise, but of course all this is entirely unnecessary. You'd get the same results in a more usable format simply by a straight query without any PL/SQL at all:

    select id,name,salary from emp;