-
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 11:17 AM.
-
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
-
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!
Last edited by see_one; 02-16-2006 at 01:04 PM.
-
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.
Last edited by WilliamR; 02-21-2006 at 02: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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|