-
Collection error!!
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.
-
Can you declare a variable outside declaration section?
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
No. Should I declare it before i use it.
-
I got that to work, thanks. I have another problem now. Before I was using a temp table in my report that would get populated before the report would run and then my report query would only show the resuly based on my temp table. Now, when I change this to my OBJECT type I cannot use my object type in my main query, to say, only get me those results that have a match in my object type table.
How will I make a link between my SQL query and this OBJECT TYPE table? I would appreciate some light on this matter or some suggestion. Thanks.
-
Incorporate the query in the procedure
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Okay. I have created my object type:
create or replace type driver_object as object (
driver_num number(15));
my type:
create or replace type driverTblType as table of driver_object;
I am trying to populate my table object in a procedure with a cursor but it keeps giving me this error.
Code:
oracle@CVL> declare
2
3 CURSOR ACTIVE_DRIVER IS
4 select driver_account_number
5 from driver
6 where contract_date < sysdate
7 and contract_expires > sysdate;
8
9
10 begin
11
12 FOR CUR IN ACTIVE_DRIVER LOOP
13
14 driverTblType := driverTblType(driver_object(CUR.DRIVER_ACCOUNT_NUMBER));
15
16 END LOOP;
17
18 end;
19 /
driverTblType := driverTblType(driver_object(CUR.DRIVER_ACCOUNT_NUMBER));
*
ERROR at line 14:
ORA-06550: line 14, column 5:
PLS-00321: expression 'DRIVERTBLTYPE' is inappropriate as the left hand side of an assignment statement
ORA-06550: line 14, column 5:
PL/SQL: Statement ignored
I have tried assigning it to a variable but it's not working. Could someone please help in this. I would really appreciate it. Thanks.
-
You can do this way
Code:
Declare
Type driver_object is table of number(15);
driverTblType driver_object;
Begin
select driver_account_number bulk collect into driverTblType
from driver where contract_date < sysdate
and contract_expires > sysdate;
Forall nums in driverTblType.First..driverTblType.Last
query whatever you want;
End;
Last edited by simply_dba; 06-09-2005 at 01:36 PM.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
thanks for helping me out. I am just frustrated now. Why can't my work the way I want it to? That's how they explain it, it's a table of record. I used yours but I don't how to write a query on it. Because ultimately I have to use it as sub select in the main query to get the result only for the number that is in my object type table. How the hell will I do that?
-
If you post your query..........
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
This is my report query:
Code:
SELECT sp.name, sp.acct_num, count(s.reg_num), count(c.claim_num), count(c.claim_num)/count(s.reg_num) as Ratio,
SUM(s.act_line_haul_amt)
FROM shipment_people sp, shipment s, claims c
WHERE sp.s_reg_num = c.reg_num(+)
AND sp.acct_num IN
(Select account_num
from active_driver_agents)
AND sp.s_reg_num = s.reg_num
AND sp.acct_num between :P_From_Acctnum and :P_To_Acctnum
AND s.act_load_date between :P_From_Date and :P_To_Date
GROUP BY sp.name, sp.acct_num
ORDER BY sp.acct_num
Look at the sub select, thats where I want to use my pl/sql table. I also read you can only use Varray in your SQL and not pl/sql tables. But the problem with VARRAYS you have to predefine the size. In my case, my for loop will change unless I do the count and use that to set the size of VARRAY. But that's another issue. You might be able to help me out with what we have right now. Thanks.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|