Havin problems with package body!

[see_one]

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.

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

[rigatoni]

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.

[see_one]

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

[see_one]

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!

[see_one]

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.

[WilliamR]

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,

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.