DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Collection error!!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    No. Should I declare it before i use it.

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

  5. #5
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

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

  7. #7
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

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

  9. #9
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

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


Click Here to Expand Forum to Full Width