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;
  /

---

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.

Thanks. I realized it and have already changes to my post. I'd appreciate if you give some ideas. Thanks man.

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! :)

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.

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.