3 Replies Latest reply: Sep 13, 2013 11:10 PM by user601042 RSS

    Sorting Nested record array

    user601042

      Hi ,

         Can you help me for sorting(based on joining date) the below nested array using TABLE function?

       

       

      declare
      type emp_rec is record
      (emp_id char(20),
      join_d date);
      type emp_tbl_type is table of emp_rec ;

      non_sorted emp_tbl_type ;
      asc_sorted emp_tbl_type;
      begin
      -- create sample data

      non_sorted(1).emp_id  := '1';
      non_sorted(1).join_d := sysdate + 5 ;
      non_sorted(2).emp_id := '2';
      non_sorted(2).join_d := sysdate + 1 ;
      non_sorted(3).emp_id  := '3';
      non_sorted(3).join_d := sysdate + 7 ;
      non_sorted(4).emp_id  := '4';
      non_sorted(4).join_d := sysdate + 7 ;

      for i in non_sorted.first .. non_sorted.last loop

      dbms_output.put_line('Emp ID:'|| non_sorted(i).emp_id || 'Date:'||non_sorted(i).join_d);
      end loop;

      select emp_id,join_d
      BULK COLLECT INTO asc_sorted
      from TABLE(cast(non_sorted AS emp_tbl_type))
      order by join_d;
      --can you sort this data and move this to asc_sorted

      for i in 1 .. asc_sorted.count loop

      dbms_output.put_line('Emp ID:'|| asc_sorted(i).emp_id || 'Date:'||asc_sorted(i).join_d);
      end loop;


      end;

       

       

      Thanks!

        • 1. Re: Sorting associate array
          rp0428

          You have to write your own query first to try to solve what appears to be your homework assignment. The forum is NOT a coding service to answer your school questions; it is to help people that are having problems with their own code.

          • 2. Re: Sorting associate array
            user601042

            Actually i tried using table function since this is simple. I got error during this time. Posted actual code.

            i'm able to acheive this by bubble sort.

             

            Thanks!

            • 3. Re: Sorting Nested record array
              user601042

              Thanks i've fixed the issue after reading oracle documentation.

               

              declare

              non_sorted emp_tbl_type := emp_tbl_type() ;
              asc_sorted emp_tbl_type := emp_tbl_type();

              /*cursor asc_cur is
              select emp_id,join_d
              from TABLE(CAST(non_sorted AS emp_tbl_type))
              order by join_d ; */


              begin
              -- create sample data
              non_sorted.extend;
              non_sorted(1) := emp_rec('1',sysdate+5);
              non_sorted.extend;
              non_sorted(2) := emp_rec('2',sysdate+1);
              non_sorted.extend;
              non_sorted(3) := emp_rec('3',sysdate+7);
              non_sorted.extend;
              non_sorted(4) := emp_rec('4',sysdate+3);

               

              for i in non_sorted.first .. non_sorted.last loop

              dbms_output.put_line('Emp ID:'|| non_sorted(i).emp_id || 'Date:'||non_sorted(i).join_d);
              end loop;

              dbms_output.put_line('********NONSORTED data');

              select emp_rec(emp_id,join_d)
              bulk collect into asc_sorted
              from TABLE(CAST(non_sorted AS emp_tbl_type))
              order by join_d;


              dbms_output.put_line('********NONSORTED data');
              for i in 1 .. asc_sorted.count loop

              dbms_output.put_line('Emp ID:'|| asc_sorted(i).emp_id || 'Date:'||asc_sorted(i).join_d);
              end loop;


              end;

               

              thx