Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: reference cursors

RE: reference cursors

From: <Stephen.Lee_at_DTAG.Com>
Date: Fri, 30 Jan 2004 13:05:20 -0600
Message-ID: <D6339830FC73944E889CC3CEADDB205B079091C9@dtagpo1.dtg.local>

I'll have to look at this when I am more awake. But ...
*I* am not trying to do anything. Somebody ELSE is trying to do something, and it's stinking up the place, and they want me to do SOMETHING to make it go faster. So I did SOMETHING. For what it's worth, here is the resulting, big, ugly thing. I trust you will interpret from my naming "conventions" what I thought about it. I not real inclined to fiddle with the original query because then I would end up owning it. (Where's my ten-foot pole.) Enjoy.

create or replace package RateRetrievalMeth as

  type doink_type is record (

     location_code time_mileage_rate.location_code%type,
     car_type time_mileage_rate.car_type%type,
     rate_code time_mileage_rate_code.rate_code%type,
     plan_code time_mileage_rate_code.plan_code%type,
     effective_date time_mileage_rate.effective_date%type,
     discontinue_date time_mileage_rate.discontinue_date%type,
     tmrrate time_mileage_rate.time_mileage_rate%type,
     extra_day_time_mile_rate_amt
time_mileage_rate.extra_day_time_mile_rate_amt%type,
     extra_hour_time_mile_rate_amt
time_mileage_rate.extra_hour_time_mile_rate_amt%type,
     mile_rate time_mileage_rate.mile_rate%type,
     mile_allow_nbr time_mileage_rate.mile_allow_nbr%type,
     extra_day_mile_allow_nbr
time_mileage_rate.extra_day_mile_allow_nbr%type,
     extra_hour_mile_allow_nbr
time_mileage_rate.extra_hour_mile_allow_nbr%type,
     pickup_return_rule time_mileage_rate_code.pickup_return_rule%type,
     text_rule time_mileage_rate_code.text_rule%type,
     guarantee_rule time_mileage_rate_code.guarantee_rule%type,
     deposit_rule time_mileage_rate_code.deposit_rule%type,
     one_way_rule time_mileage_rate_code.one_way_rule%type,
     point_of_sale_rule time_mileage_rate_code.point_of_sale_rule%type,
     inclusive_rule time_mileage_rate_code.inclusive_rule%type,
     description_text time_mileage_rate_code.description_text%type,
     minimum_adv_required_days
time_mileage_rate_code.minimum_adv_required_days%type,
     minimum_adv_required_hrs
time_mileage_rate_code.minimum_adv_required_hrs%type,
     minimum_rate_code time_mileage_rate_code.minimum_rate_code%type,
     minimum_time_mileage_plan_code
time_mileage_rate_code.minimum_time_mileage_plan_code%type,
     category_code_clone time_mileage_rate.category_code_clone%type,
     sell_rule time_mileage_rate_code.sell_rule%type,
     rental_agreement_segment_code
time_mileage_rate_code.rental_agreement_segment_code%type,
     currency_code time_mileage_rate_code.currency_code%type,
     commission_pct time_mileage_rate_code.commission_pct%type,
     commission_amt time_mileage_rate_code.commission_amt%type,
     allow_discount_flag time_mileage_rate_code.allow_discount_flag%type
  );

  type doink_table is table of doink_type;

--cursor MultiRates is
-- SELECT location_code, car_type,tmri.rate_code tmrirate,tmri.plan_code tmricode,tmr.effective_date tmreffdate,
-- tmr.discontinue_date tmrdisdate,tmr.time_mileage_rate tmrrate,extra_day_time_mile_rate_amt,extra_hour_time_mile_rate_amt,

--
mile_rate,mile_allow_nbr,extra_day_mile_allow_nbr,extra_hour_mile_allow_nbr,
pickup_return_rule,
--
text_rule,guarantee_rule,deposit_rule,one_way_rule,point_of_sale_rule,inclus
ive_rule,description_text,
--
minimum_adv_required_days,minimum_adv_required_hrs,minimum_rate_code,minimum
_time_mileage_plan_code,
--
tmr.category_code_clone,sell_rule,rental_agreement_segment_code,currency_cod
e,commission_pct,
--        commission_amt,allow_discount_flag
--   FROM time_mileage_rate tmr, time_mileage_rate_code tmri;

cursor MultiRates is select * from duct_tape;

  type doink_cursor is ref cursor return MultiRates%rowtype;
-- type doink_cursor is ref cursor return doink_type;

  procedure GetRates (
     sLocCode in time_mileage_rate.location_code%type,
     nMinAdvReqDays in
time_mileage_rate_code.minimum_adv_required_days%type,
     nMinAdvReqHrs  in time_mileage_rate_code.minimum_adv_required_hrs%type,
     sEffDate       in varchar2,
     sDisconDate    in varchar2,
     sTMRateCode    in varchar2,
     sCatagory      in varchar2,
     sPlanCode      in varchar2,
     sCarType       in varchar2,
     cRateRet       out doink_cursor,
     nReturn        out number);

end;

/
create or replace package body RateRetrievalMeth as procedure GetRates ( sLocCode in time_mileage_rate.location_code%type, nMinAdvReqDays in time_mileage_rate_code.minimum_adv_required_days%type, nMinAdvReqHrs in time_mileage_rate_code.minimum_adv_required_hrs%type, sEffDate in varchar2, sDisconDate in varchar2, sTMRateCode in varchar2, sCatagory in varchar2, sPlanCode in varchar2, sCarType in varchar2, cRateRet out doink_cursor, nReturn out number ) as the_table doink_table; begin execute immediate 'truncate table xxxxxx.duct_tape'; SELECT /*+ NO_EXPAND INDEX (tmr TMR_LCT_FK_IDX) INDEX (tmri TMRC_TEST_PROC) cardinality (t4, 10) cardinality (t3, 10) cardinality (t2, 10) cardinality (t1, 10) */ location_code, car_type, tmri.rate_code tmrirate, tmri.plan_code tmricode, tmr.effective_date tmreffdate, tmr.discontinue_date tmrdisdate, tmr.time_mileage_rate tmrrate, extra_day_time_mile_rate_amt, extra_hour_time_mile_rate_amt, mile_rate, mile_allow_nbr, extra_day_mile_allow_nbr, extra_hour_mile_allow_nbr, pickup_return_rule, text_rule, guarantee_rule, deposit_rule, one_way_rule, point_of_sale_rule, inclusive_rule, description_text, minimum_adv_required_days, minimum_adv_required_hrs, minimum_rate_code, minimum_time_mileage_plan_code, tmr.category_code_clone, sell_rule, rental_agreement_segment_code, currency_code, commission_pct, commission_amt, allow_discount_flag BULK COLLECT INTO the_table FROM time_mileage_rate tmr, time_mileage_rate_code tmri WHERE tmr.rate_code = tmri.rate_code AND tmr.plan_code = tmri.plan_code AND tmr.location_code = sLocCode AND tmri.minimum_adv_required_days <= nMinAdvReqDays AND tmri.minimum_adv_required_hrs <= nMinAdvReqHrs AND tmr.effective_date <= TO_DATE (sEffDate, 'YYYY-MM-DD') AND (tmr.discontinue_date >= TO_DATE (sDisconDate, 'YYYY-MM-DD') OR tmr.discontinue_date IS NULL ) AND (tmr.rate_code IN (select /*+ cardinality (t1, 10) */ column_value from table (in_list(sTMRateCode)) t1 where rownum > 0) OR tmr.category_code_clone IN (select /*+ cardinality (t2, 10) */ column_value from table (in_list(sCatagory)) t2 where rownum > 0) ) AND tmr.plan_code IN (select /*+ cardinality (t3, 10) */ column_value from table (in_list(sPlanCode)) t3 where rownum > 0) AND tmr.car_type IN (select /*+ cardinality (t4, 10) */ column_value from table (in_list(sCarType)) t4 where rownum > 0) ORDER BY location_code, tmri.rate_code, tmri.plan_code, tmr.car_type; for i in 1 .. the_table.count loop insert into duct_tape values( the_table(i).location_code, the_table(i).car_type, the_table(i).rate_code, the_table(i).plan_code, the_table(i).effective_date, the_table(i).discontinue_date, the_table(i).tmrrate, the_table(i).extra_day_time_mile_rate_amt, the_table(i).extra_hour_time_mile_rate_amt, the_table(i).mile_rate, the_table(i).mile_allow_nbr, the_table(i).extra_day_mile_allow_nbr, the_table(i).extra_hour_mile_allow_nbr, the_table(i).pickup_return_rule, the_table(i).text_rule, the_table(i).guarantee_rule, the_table(i).deposit_rule, the_table(i).one_way_rule, the_table(i).point_of_sale_rule, the_table(i).inclusive_rule, the_table(i).description_text, the_table(i).minimum_adv_required_days, the_table(i).minimum_adv_required_hrs, the_table(i).minimum_rate_code, the_table(i).minimum_time_mileage_plan_code, the_table(i).category_code_clone, the_table(i).sell_rule, the_table(i).rental_agreement_segment_code, the_table(i).currency_code, the_table(i).commission_pct, the_table(i).commission_amt, the_table(i).allow_discount_flag ); end loop; commit; open cRateRet for select * from duct_tape; nReturn := 0; exception when others then nReturn := sqlcode; end; end;
/
-----Original Message----- I know Jonathan Lewis had some other suggestions. I am still going to add my two cents. Maybe I don't understand what you're trying to do, but is this what you're looking for? drop table t1 ; drop table t2 ; drop type t_tab_type ; drop type t_type ; drop package my_types ; drop function f ; create table t1 (n number, v varchar2 (30)) ; create table t2 (n number, v varchar2 (30)) ; insert into t1 (n, v) values (1, 'ONE') ; insert into t2 (n, v) values (2, 'TWO') ; insert into t2 (n, v) values (3, 'THREE') ; commit ; create type t_type as object (value number, name varchar2 (30) ) ;
/
create type t_tab_type as table of t_type ;
/
create package my_types as type c_ref is ref cursor ; end my_types ;
/
create function f (table_num_in in number) return my_types.c_ref is t_data t_tab_type := t_tab_type () ; rc my_types.c_ref ; begin if table_num_in = 1 then for t_rec in (select n, v from t1) loop t_data.extend ; t_data (t_data.count) := t_type (t_rec.n, t_rec.v) ; end loop ; elsif table_num_in = 2 then for t_rec in (select n, v from t2) loop t_data.extend ; t_data (t_data.count) := t_type (t_rec.n, t_rec.v) ; end loop ; end if ; open rc for select value, name from table (cast (t_data as t_tab_type)) ; return rc ; end f ;
/
SQL> variable x refcursor SQL> execute :x := f (1) Procédure PL/SQL terminée avec succès. SQL> print x VALUE NAME --------- ------------------------------ 1 ONE SQL> execute :x := f(2) Procédure PL/SQL terminée avec succès. SQL> print x VALUE NAME --------- ------------------------------ 2 TWO 3 THREE SQL> ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
Received on Fri Jan 30 2004 - 13:05:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US