Havin problems with package body!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Havin problems with package body!

  1. #1
    Join Date
    Jun 2004
    Posts
    125

    Smile Havin problems with package body!

    Hi guys,

    Okay, I have edited my post to make it easier for all of you guys. I know I'm gonna find a way to do it,whether I get any ideas from you guys or find it on my own. So, here it goes...

    Actually, I need to find some kind of function that allows me to manipulate with column names or tell to my record type to populate state rates for my rows. I found some useful function with DBMS_SQL.DEFINE_COLUMN but not really looked into it.

    I think this id going to be a good exercise for everyone . Thanks guys and gals.

    Code:
    CREATE OR REPLACE PACKAGE rate_pkg IS
    
    TYPE rate_rec IS RECORD (
        id
        ,cycle_rates.id%TYPE
        ,cycle	cycle_rates.cycle%TYPE
        ,scac	cycle_rates.scac%TYPE
        ,gbloc	cycle_rates.gbloc%TYPE
        ,code	cycle_rates.code%TYPE
        ,AK_PCT	cycle_rates.rate_pct%TYPE
        ,AL_PCT	cycle_rates.rate_pct%TYPE
        ,AR_PCT	cycle_rates.rate_pct%TYPE
        ,AZ_PCT	cycle_rates.rate_pct%TYPE
        ,CA_PCT	cycle_rates.rate_pct%TYPE
        ,CO_PCT	cycle_rates.rate_pct%TYPE
        ,CT_PCT	cycle_rates.rate_pct%TYPE
        ,DC_PCT	cycle_rates.rate_pct%TYPE
        ,DE_PCT	cycle_rates.rate_pct%TYPE
        ,FL_PCT	cycle_rates.rate_pct%TYPE
        ,GA_PCT	cycle_rates.rate_pct%TYPE
        ,IA_PCT	cycle_rates.rate_pct%TYPE
        ,ID_PCT	cycle_rates.rate_pct%TYPE
        ,IL_PCT	cycle_rates.rate_pct%TYPE
        ,IN_PCT	cycle_rates.rate_pct%TYPE
        ,KS_PCT	cycle_rates.rate_pct%TYPE
        ,KY_PCT	cycle_rates.rate_pct%TYPE
        ,LA_PCT	cycle_rates.rate_pct%TYPE
        ,MA_PCT	cycle_rates.rate_pct%TYPE
        ,MD_PCT	cycle_rates.rate_pct%TYPE
        ,ME_PCT	cycle_rates.rate_pct%TYPE
        ,MI_PCT	cycle_rates.rate_pct%TYPE
        ,MN_PCT	cycle_rates.rate_pct%TYPE
        ,MO_PCT	cycle_rates.rate_pct%TYPE
        ,MS_PCT	cycle_rates.rate_pct%TYPE
        ,MT_PCT	cycle_rates.rate_pct%TYPE
        ,NC_PCT	cycle_rates.rate_pct%TYPE
        ,ND_PCT	cycle_rates.rate_pct%TYPE
        ,NE_PCT	cycle_rates.rate_pct%TYPE
        ,NH_PCT	cycle_rates.rate_pct%TYPE
        ,NJ_PCT	cycle_rates.rate_pct%TYPE
        ,NM_PCT	cycle_rates.rate_pct%TYPE
        ,NV_PCT	cycle_rates.rate_pct%TYPE
        ,NY_PCT	cycle_rates.rate_pct%TYPE
        ,OH_PCT	cycle_rates.rate_pct%TYPE
        ,OK_PCT	cycle_rates.rate_pct%TYPE
        ,OR_PCT	cycle_rates.rate_pct%TYPE
        ,PA_PCT	cycle_rates.rate_pct%TYPE
        ,RI_PCT	cycle_rates.rate_pct%TYPE
        ,SC_PCT	cycle_rates.rate_pct%TYPE
        ,SD_PCT	cycle_rates.rate_pct%TYPE
        ,TN_PCT	cycle_rates.rate_pct%TYPE
        ,TX_PCT	cycle_rates.rate_pct%TYPE
        ,UT_PCT	cycle_rates.rate_pct%TYPE
        ,VA_PCT	cycle_rates.rate_pct%TYPE
        ,VT_PCT	cycle_rates.rate_pct%TYPE
        ,WA_PCT	cycle_rates.rate_pct%TYPE
        ,WI_PCT	cycle_rates.rate_pct%TYPE
        ,WV_PCT	cycle_rates.rate_pct%TYPE
        ,WY_PCT	cycle_rates.rate_pct%TYPE
        );
    
      TYPE cycle_rate_tab IS TABLE OF rate_rec INDEX BY BINARY_INTEGER;
    
    --Statement below needed if using Ref Cursor
    PROCEDURE rate_tab_qry(rate_data IN OUT cycle_rate_tab,
      		         p_id cycle_rates.id%TYPE,
      		         p_cycle cycle_rates.cycle%TYPE,
      		         p_scac cycle_rates.scac%TYPE,
      		         p_gbloc cycle_rates.gbloc%TYPE,
      		         p_code cycle_rates.code%TYPE);
    
    
    --rates package body
    CREATE OR REPLACE PACKAGE BODY rate_pkg IS
    
    PROCEDURE rate_tab_qry(rate_data IN OUT cycle_rate_tab,
      		         p_id cycle_rates.id%TYPE,
      		         p_cycle cycle_rates.cycle%TYPE,
      		         p_scac cycle_rates.scac%TYPE,
      		         p_gbloc cycle_rates.gbloc%TYPE,
      		         p_code cycle_rates.code%TYPE)
    IS 
    
    idx	PLS_INTEGER := 1;
    cnt        PLS_INTEGER := 6;
    
    CURSOR c_rates IS
    SELECT rate_pct
    FROM cycle_rates
    WHERE cycle = p_cycle
    AND scac = p_scac
    AND gbloc = p_gbloc
    AND code = p_code;
    
    
    BEGIN
    	--these are first 5 columns in my record TYPE
    	rate_data(idx) := p_id;
    	rate_data(idx) := p_cycle;
    	rate_data(idx) := p_scac;
    	rate_data(idx) := p_gbloc;
    	rate_data(idx) := p_code;
    	             FOR cur_rates in c_rates
                --somehow I need to tell it to go populate it from col 6 
    and col 50 for my state rates column
             
    		LOOP
                               rate_data(idx)(cnt) := cur_rates.rate_pct;
      
         	               cnt := cnt + 1;
         	             END LOOP;     		
      END rate_tab_qry;
      END;
      /
    Last edited by see_one; 02-16-2006 at 10:17 AM.

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    I'm not sure you want a ref cursor here. I believe you can only use them to pull data via a query and not as a target for assigning values. It looks like you need either an array or a plsql table that hold multiple values.

    Perhaps something like would work:

    TYPE rate_rec IS RECORD (
    id cycle_rates.id%TYPE
    ,cycle cycle_rates.cycle%TYPE
    ,scac cycle_rates.scac%TYPE
    ,gbloc cycle_rates.gbloc%TYPE
    ,code cycle_rates.code%TYPE
    ,AK_PCT cycle_rates.rate_pct%TYPE
    ,AL_PCT cycle_rates.rate_pct%TYPE
    ,AR_PCT cycle_rates.rate_pct%TYPE
    ,AZ_PCT cycle_rates.rate_pct%TYPE
    ,CA_PCT cycle_rates.rate_pct%TYPE
    ,CO_PCT cycle_rates.rate_pct%TYPE
    ,CT_PCT cycle_rates.rate_pct%TYPE
    ,DC_PCT cycle_rates.rate_pct%TYPE
    ,DE_PCT cycle_rates.rate_pct%TYPE
    ,FL_PCT cycle_rates.rate_pct%TYPE
    ,GA_PCT cycle_rates.rate_pct%TYPE
    ,IA_PCT cycle_rates.rate_pct%TYPE
    ,ID_PCT cycle_rates.rate_pct%TYPE
    ,IL_PCT cycle_rates.rate_pct%TYPE
    ,IN_PCT cycle_rates.rate_pct%TYPE
    ,KS_PCT cycle_rates.rate_pct%TYPE
    ,KY_PCT cycle_rates.rate_pct%TYPE
    ,LA_PCT cycle_rates.rate_pct%TYPE
    ,MA_PCT cycle_rates.rate_pct%TYPE
    ,MD_PCT cycle_rates.rate_pct%TYPE
    ,ME_PCT cycle_rates.rate_pct%TYPE
    ,MI_PCT cycle_rates.rate_pct%TYPE
    ,MN_PCT cycle_rates.rate_pct%TYPE
    ,MO_PCT cycle_rates.rate_pct%TYPE
    ,MS_PCT cycle_rates.rate_pct%TYPE
    ,MT_PCT cycle_rates.rate_pct%TYPE
    ,NC_PCT cycle_rates.rate_pct%TYPE
    ,ND_PCT cycle_rates.rate_pct%TYPE
    ,NE_PCT cycle_rates.rate_pct%TYPE
    ,NH_PCT cycle_rates.rate_pct%TYPE
    ,NJ_PCT cycle_rates.rate_pct%TYPE
    ,NM_PCT cycle_rates.rate_pct%TYPE
    ,NV_PCT cycle_rates.rate_pct%TYPE
    ,NY_PCT cycle_rates.rate_pct%TYPE
    ,OH_PCT cycle_rates.rate_pct%TYPE
    ,OK_PCT cycle_rates.rate_pct%TYPE
    ,OR_PCT cycle_rates.rate_pct%TYPE
    ,PA_PCT cycle_rates.rate_pct%TYPE
    ,RI_PCT cycle_rates.rate_pct%TYPE
    ,SC_PCT cycle_rates.rate_pct%TYPE
    ,SD_PCT cycle_rates.rate_pct%TYPE
    ,TN_PCT cycle_rates.rate_pct%TYPE
    ,TX_PCT cycle_rates.rate_pct%TYPE
    ,UT_PCT cycle_rates.rate_pct%TYPE
    ,VA_PCT cycle_rates.rate_pct%TYPE
    ,VT_PCT cycle_rates.rate_pct%TYPE
    ,WA_PCT cycle_rates.rate_pct%TYPE
    ,WI_PCT cycle_rates.rate_pct%TYPE
    ,WV_PCT cycle_rates.rate_pct%TYPE
    ,WY_PCT cycle_rates.rate_pct%TYPE
    );

    TYPE cycle_rate_tbl is table of rate_rec
    index by binary_integer;


    You should then be able to reference a variable defined on the cycle_rate_tbl type in your procedure.
    ____________________
    Pete

  3. #3
    Join Date
    Jun 2004
    Posts
    125
    Thanks. I realized it and have already changes to my post. I'd appreciate if you give some ideas. Thanks man.

  4. #4
    Join Date
    Jun 2004
    Posts
    125

    Smile

    Okay! Basically to sum up. I need to know how to dynamically create field names that are in my record type.

    For example,

    FOR cur_rate IN c_rate
    LOOP
    rate_data(1).field_name := cur_rate.rate_pct;
    --field_name = AK_PCT, AL_PCT and so on...
    END LOOP;

    The other possibility that comes to mind is to create an array or a package that would allow me to specifically pinpoint the column number in array. I just haven't worked so much with collection type and I really don't feel I am well-equiped as to how to go about all this. Just a thought!
    Last edited by see_one; 02-16-2006 at 12:04 PM.

  5. #5
    Join Date
    Jun 2004
    Posts
    125

    Thumbs up

    Let me just ask this. Is there a way in PL/SQL that would allow me to use subscript for coulmns, as we have for rows? Thanks.

  6. #6
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    No, but you can refer to elements of an array by subscript. Perhaps instead of 50 rate attributes you could have 1 array with 50 elements (one for 'AK', one for 'AL' and so on). For example,
    Code:
    TYPE rate_pct_tt IS TABLE OF cycle_rates.rate_pct%TYPE INDEX BY VARCHAR2(2);
    
    TYPE rate_rec IS RECORD
    ( id cycle_rates.id%TYPE
    , cycle cycle_rates.cycle%TYPE
    , scac cycle_rates.scac%TYPE
    , gbloc cycle_rates.gbloc%TYPE
    , code cycle_rates.code%TYPE
    , rates rate_pct_tt );
    
    v_rate  rate_rec;
    
    ...
    
    v_rate.rates('AK') := 12.34;
    Now you can loop around that array using FIRST, NEXT, LAST etc as normal.
    Last edited by WilliamR; 02-21-2006 at 01:42 PM.

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