I am trying to write a report which creates a temp table before it is run and show result based on that temp table. Now, I want to change this and do the same on using PL/SQL table or collection. Because the table gets deleted and populated everytime the report is run.

Below is my OBJECT TYPE and my Procedure that uses my type.

Code:
CREATE OR REPLACE TYPE ACCOUNT_NUM_T AS OBJECT(DRIVER_NUM NUMBER(15));

Type created.

MY PROCEDURE:

  1  DECLARE
  2  CURSOR ACTIVE_DRIVER IS
  3  select driver_account_number
  4  from driver
  5  where contract_date < sysdate
  6  and contract_expires > sysdate;
  7  CURSOR ACTIVE_AGENTS IS
  8  select account_num
  9  from f01names2
 10  where (account_num between 1 and 1900 OR account_num between 4000 and 6700);
 11  BEGIN
 12  FOR CUR IN ACTIVE_DRIVER LOOP
 13   ACCT_NUM_REC ACCOUNT_NUM_T := ACCOUNT_NUM_T(CUR.DRIVER_ACCOUNT_NUMBER);
 14  END LOOP;
 15  FOR CUR IN ACTIVE_AGENTS LOOP
 16   ACCT_NUM_REC ACCOUNT_NUM_T := ACCOUNT_NUM_T(CUR.ACCOUNT_NUM);
 17  END LOOP;
 18* END;
[email protected]> /
 ACCT_NUM_REC ACCOUNT_NUM_T := ACCOUNT_NUM_T(CUR.DRIVER_ACCOUNT_NUMBER);
              *
ERROR at line 13:
ORA-06550: line 13, column 15:
PLS-00103: Encountered the symbol "ACCOUNT_NUM_T" when expecting one of the following:
:= . ( @ % ;
The symbol "." was substituted for "ACCOUNT_NUM_T" to continue.
ORA-06550: line 16, column 15:
PLS-00103: Encountered the symbol "ACCOUNT_NUM_T" when expecting one of the following:
:= . ( @ % ;
The symbol "." was substituted for "ACCOUNT_NUM_T" to continue.
If you guys take a look at this and tell me how I should use my collection so that I won't have to deal with table. Thanks.