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;
[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.

Can you declare a variable outside declaration section?

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

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;

---

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..........

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.

I'm still waiting for your reply simply_dba.

Try something like this

SQL> Create or replace type driver_object is table of number(15)
/




create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();

begin

<<< your code to populate myobj >>

return myobj;


end;
/

then in report


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 column_value acct_num from table (cast (driv_func as driver_object)))

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

/

Hope this helps

Hey Bonker,

I really appreciate your help. You helped me in other post. Thanks.

I used your example for this post. I have created the object type and the function and populating my myobj table in my driv_func. I also have that sub select in my report. Do i need to call my function in Before Report Trigger to get it populated or, sub select will call it as you are casting. I have put the sub select in my report query but there is no output my report is empty. I have everything below :

Code:

---

Create or replace type driver_object is table of number(15);

created.



create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();

CURSOR ACTIVE_DRIVER IS
select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate;

CURSOR ACTIVE_AGENTS IS
select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between 4000 and 6700);

begin

FOR CUR IN ACTIVE_DRIVER
LOOP
 
        myobj := driver_object(CUR.DRIVER_ACCOUNT_NUMBER);
 
END LOOP;

FOR CUR IN ACTIVE_AGENTS
LOOP
 
        myobj := driver_object(CUR.ACCOUNT_NUM);
END LOOP;

return myobj;

end;
/

Function created.

In my report query I have this, it seems it accepts it fine:

SELECT trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), 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 column_value acct_num from table (cast (driv_func as driver_object)))

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 trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), sp.acct_num
ORDER BY sp.acct_num

---

EDITED:
When I run this select statement in SQL PLUS. I would assume it should give me a list of account_number. I get this:

1* Select column_value acct_num from table (cast (driv_func as driver_object))
[email protected]> /

ACCT_NUM
----------
1539

Elapsed: 00:00:00.81
[email protected]>

EDITED
I also would like to know in my sub select acct_num is the alias or what. I'd appreciate your reply on this one. It would really help me accomplish things in a newer way. Thanks.

I just checked even the count from both cursors is somewhere 3000. I don't know whats going on, man. I really want to complete this thing for learning, at least. Thanks a whole lot.

Okay. I know whats happening. Bonker you need to read my two posts before this one. I comment out one cursor and used just the first one. The count I gave you earlier was not the count it was a value in my object table. If I use the return the object outside my For Loop, as u used it, it returns the value in the first record.

So, I thought I should put it inside of the loop to keep returning the entire thing. Now, it return the last record. I don't know what's the deal with PL/SQL. I am just tired of figuring this small **** out. Damn it! I know, I have already posted so many times, u may reply whenever u have time. Thanks a lot.

create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();

CURSOR ACTIVE_DRIVER IS
select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate;

CURSOR ACTIVE_AGENTS IS
select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between 4000 and 6700);

begin

FOR CUR IN ACTIVE_DRIVER
LOOP
myobj.extend;

myobj(myobj.count) := CUR.DRIVER_ACCOUNT_NUMBER;

END LOOP;

FOR CUR IN ACTIVE_AGENTS
LOOP

myobj.extend;

myobj(myobj.count) := CUR.ACCOUNT_NUM ;
END LOOP;

return myobj;


end;
/

Yes in subquery I have just called column_value with alias as acct_num

Sorry see_one I coudn't help you out on this bit.
Hi bonker,
can you expalin this one

Quote:

---

myobj.extend;

myobj(myobj.count) := CUR.DRIVER_ACCOUNT_NUMBER;

---

Hi simply_dba,

The myobj.extend allocates space to this table type and then assigns value with myobj(myobj.count) := <>

i.e. assign value in the new space created and thus assigns all the value in cursor as records in this table.


this could have also done by using bulk collect i.e.

select * bulk collect into myobj from

( select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate


Hope it explains.

Hi simply_dba,

The myobj.extend allocates space to this table type and then assigns value with myobj(myobj.count) := <>

i.e. assign value in the new space created and thus assigns all the value in cursor as records in this table.


this could have also done by using bulk collect i.e.


create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();

begin

select * bulk collect into myobj from

( select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate
union all
select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between
4000 and 6700)
);

return myobj;

end;
/



Hope it explains.

YES! It works like a charm, Bonker. I really appreciate you helping me with this thing. I learned this not for the this report, but for my future projects. I work for a small company and I have to struggle for things like this on my own and to keep learning things for my career's sake. I hope this has helped other members here as well. I want to thank simply_dba and, of course, Bonker. I'm really excited. See you next time.

So, which would be faster performance wise. I mean, bulk collect or what I have. Thanks.

Quote:

---

So, which would be faster performance wise. I mean, bulk collect or what I have

---

that you have to benchmark in your system and test and use the one which is performant.

I, however based on your report query that you have posted, fail to see why do you really need the table type object and function to return such SQLTYPE.

Your report query would simply look like this



SELECT trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), 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 driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate

union all

select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between 4000 and 6700)

)



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 trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), sp.acct_num
ORDER BY sp.acct_num

i.e no need to write function return table type etc.....

After all this, u have knocked me out man. You are right! When would you use something like this. My other reason for doing all this is to know this, so that when I move somewhere else, I, at least, know these basic things. I would appreciate if you would recommend a book or something. But I know these things u learn from experience and working with a good bunch of people. I'll use union because that should be the fastest. Thanks a lot.

Quote:

---

I'll use union because that should be the fastest

---

No do not do that. THe Union will infact run slower because Union will remove duplicate records and just give distinct rowsets from the two queries. If you really want to have distinct records then use UNION but for perfomance wise no.... It will be slower compared to Union all....

The books you can read is Expert one on one by Tom Kyte or Practical Oracle 8i by Jonathan Lewis. Both these books are excellent books to Know how Oracle Works....

Hi Bonker,

If it's not much of a trouble to you, would u give me your yahoo ID or something? May be, once in a while, when I'm stuck with something or want to learn something I may ask for you. By the way, I don't chat a lot but I can, at least, keep in touch with you, u know. Thanks.

My yahoo id: lynx_2050

If u add me, I'll know. thanks

I also do not Chat often. My mail id is [email protected]

Okay. I ran the union on SQL PLUS. It only runs for the second query and not for the first. I have not change the report yet. I am just trying.

EDITED:
I don'nt know, why union does not work? Both columns are the same data type and the same size too.
END.

Quote:

---

Okay. I ran the union on SQL PLUS. It only runs for the second query and not for the first. I have not change the report yet. I am just trying

---

so if use Union all what happens?

Sorry, man. I am using a login sript for my sql plus and, I think, my UNION ALL was hanging in the middle with some space in between of my two selects. So, it was not running my first query. Now it's fine. As you said, UNION all will be slower right?

Nope I said UNION will be slower compared to UNION ALL

Got it. Okay I ran bith with UNION and UNION ALL.

With UNION:

It ran the second query first, and then the first.
Time it took: Elapsed: 00:00:14.94

With UNION ALL:

It ran the first query and then the second:
Time it took: Elapsed: 00:00:14.06

With my Select for the function
Time it took: Elapsed: 00:00:14.50

So, should I change my function to have UNION ALL, instead of using a cursor?

Quote:

---

So, should I change my function to have UNION ALL, instead of using a cursor?

---

Follow Tom Kyte's Mantra http://asktom.oracle.com/pls/ask/f?p...:1205168148688 :

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as little PLSQL as you can.
o if you cannot, because plsql cannot do it, try some java (eg: sending an email
with an attachment)
o if you cannot do it in java (too slow, or you have some existing 3gl code
already) do it in C as an extproc.

I discern from his Mantra that I should use UNION ALL in my report, instead of nested table. I thought theier website went down. This new looks really cool. Thanks for helping.