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