DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 33

Thread: Collection error!!

  1. #11
    Join Date
    Jun 2004
    Posts
    125
    I'm still waiting for your reply simply_dba.

  2. #12
    Join Date
    May 2005
    Posts
    31
    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
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  3. #13
    Join Date
    Jun 2004
    Posts
    125
    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))
    oracle@OLD.CVL> /

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

    Elapsed: 00:00:00.81
    oracle@OLD.CVL>

    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.
    Last edited by see_one; 06-15-2005 at 03:22 PM.

  4. #14
    Join Date
    Jun 2004
    Posts
    125
    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.

  5. #15
    Join Date
    Jun 2004
    Posts
    125
    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.

  6. #16
    Join Date
    May 2005
    Posts
    31
    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
    Last edited by Bonker; 06-16-2005 at 04:45 AM.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  7. #17
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Sorry see_one I coudn't help you out on this bit.
    Hi bonker,
    can you expalin this one
    myobj.extend;

    myobj(myobj.count) := CUR.DRIVER_ACCOUNT_NUMBER;
    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

  8. #18
    Join Date
    May 2005
    Posts
    31
    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.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  9. #19
    Join Date
    May 2005
    Posts
    31
    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.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  10. #20
    Join Date
    Jun 2004
    Posts
    125
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width