PROCEDURE report (
i_cid  IN NUMBER
)
IS
    test1 tab_tests := tab_tests();
    test2 tab_tests;
BEGIN

-- I populate test1 with the data and it works fine

-- But when I tried to make a sorted nested table with the following command 
I got this error 'ORA-00902: invalid datatype', is there any other efficient way to sort 
a nested table than the following, like a built in Oracle collection method?
   execute immediate 'select cast( multiset (select * from table(test1) order by 1) as tab_tests) INTO test2 FROM dual';
END;

SQL> create type tab_tests is table of NUMBER(15);
  2  /

Type created.

SQL> create or replace PROCEDURE report (
  2  i_cid  IN NUMBER
  3  )
  4  IS
  5      test1 tab_tests := tab_tests();
  6      test2 tab_tests;
  7  BEGIN
  8    select * bulk collect into test2 from table(test1) order by 1;
  9  END;
 10  /

Procedure created.

SQL> create type tab_tests is table of NUMBER(15);
  2  /

Type created.

SQL> create or replace package pkg1
  2  as
  3  type tab_tests is table of NUMBER(15);
  4  PROCEDURE report(i_cid  IN NUMBER
  5  );
  6  end;
  7  /

Package created.

SQL> create or replace package body pkg1
  2  as
  3  PROCEDURE report (
  4  i_cid  IN NUMBER
  5  )
  6  IS
  7      test1 tab_tests := tab_tests();
  8      test2 tab_tests;
  9  BEGIN
 10  
 11  select * bulk collect into test2 from table(test1) order by 1;
 12  end;
 13  end;
 14  /

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY PKG1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/1     PL/SQL: SQL Statement ignored
11/39    PL/SQL: ORA-22905: cannot access rows from a non-nested table
         item

11/45    PLS-00642: local collection types not allowed in SQL statements
SQL> create or replace package body pkg1
  2  as
  3  PROCEDURE report (
  4  i_cid  IN NUMBER
  5  )
  6  IS
  7      test1 scott.tab_tests := scott.tab_tests();
  8      test2 scott.tab_tests;
  9  BEGIN
 10  
 11  select * bulk collect into test2 from table(test1) order by 1;
 12  end;
 13  end;
 14  /

Package body created.

SQL> 

 -- I have the data in test2 in a sorted order
 for i in test2.LAST .. test2.FIRST
    LOOP
        DBMS_OUTPUT.PUT_LINE(test2(i));
    END LOOP;

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE tab_tests IS TABLE OF NUMBER(15);
  2  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE report
  2    (i_cid  IN NUMBER)
  3  IS
  4    test1 tab_tests := tab_tests();
  5    test2 tab_tests;
  6  BEGIN
  7    test1 := tab_tests (1, 3, 4);
  8    SELECT * BULK COLLECT
  9    INTO   test2
 10    FROM   TABLE (test1)
 11    ORDER  BY 1;
 12    DBMS_OUTPUT.PUT_LINE ('ascending');
 13    FOR i IN 1 .. test2.LAST LOOP
 14  	 DBMS_OUTPUT.PUT_LINE (test2 (i));
 15    END LOOP;
 16    DBMS_OUTPUT.PUT_LINE ('----------');
 17    DBMS_OUTPUT.PUT_LINE ('descending');
 18    FOR i IN REVERSE 1 .. test2.LAST LOOP
 19  	 DBMS_OUTPUT.PUT_LINE (test2 (i));
 20    END LOOP;
 21  END report;
 22  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> EXEC report (0)
ascending
1
3
4
----------
descending
4
3
1

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2>

i := test.FISRT
LOOP 
  EXIT WHEN i IS NULL;
  <do something>
  i := test.NEXT(i);
END LOOP;

i := test.FISRT
WHILE i IS NOT NULL LOOP
  <do something>
  i := test.NEXT(i);
END LOOP;

SQL> DECLARE
  2    t tab_tests := tab_tests();
  3  BEGIN
  4    t.extend (4);
  5    t.delete (1);
  6    t.delete (3);
  7    t(2) := 1;
  8    t(4) := 2;
  9    FOR i in 1..t.LAST LOOP
 10      dbms_output.put_line ('t('||i||'): '||t(i));
 11    END LOOP;
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 10


SQL> DECLARE
  2    i pls_integer;
  3    t tab_tests := tab_tests();
  4  BEGIN
  5    t.extend (4);
  6    t.delete (1);
  7    t.delete (3);
  8    t(2) := 1;
  9    t(4) := 2;
 10    i := t.FIRST;
 11    WHILE i IS NOT NULL LOOP
 12      dbms_output.put_line ('t('||i||'): '||t(i));
 13      i := t.NEXT(i);
 14    END LOOP;
 15  END;
 16  /
t(2): 1
t(4): 2

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    i pls_integer;
  3    t tab_tests := tab_tests();
  4  BEGIN
  5    t.extend (4);
  6    t.delete(1);
  7    t.delete(3);
  8    t(2) := 1;
  9    t(4) := 2;
 10    i := t.LAST;
 11    WHILE i IS NOT NULL LOOP
 12      dbms_output.put_line ('t('||i||'): '||t(i));
 13      i := t.PRIOR(i);
 14    END LOOP;
 15  END;
 16  /
t(4): 2
t(2): 1

PL/SQL procedure successfully completed.