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;
oracle@OLDPEAT.CVL> /
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.