Re: Seperate two procedures

Joe wrote:
> Guru,
>
> I had a package I want to use to do an audit. The
> package has a procedure and I have renamed the package
> to a procedure and seperated few procedures. Right now
> I have a procedure inside a procedure and I want
> either make it as one procedure and use the same code
> or seperate it to a seperate procedure. I treid few methods and it
> failed. How do I do
> that. I am attaching the procedure:
> CREATE OR REPLACE procedure Check_For_Gabriel_Holes( in_netId number,
> in_year number, in_qtr number ) is
> ld_snapshotDateTime date := sysdate;
> cursor lc_GabrielAiring( in_typeId number ) is
> select ld_snapshotDateTime
> ,sell_sch_id net_id
> ,ss.s_week+psab.day bday
> ,ss.s_offset offset
> ,p.len len
> ,ss.s_week week_of
> -- Start time is offset + start of day + plus partial sid
> offset
> ,ss.s_week
> +psab.day
> +(nettm.broadcast_begin_time
> +ss.s_offset
> +ss.bgn_offset
> )/86400 start_dttm
> -- End time is offset + start of day + plus partial sid end
> offset if present or len
> ,ss.s_week
> +psab.day
> +(nettm.broadcast_begin_time
> +ss.s_offset
> -- throw out any spare seconds from the pp blocks that
> sometimes arise
> +trunc(decode(ss.end_offset,0,p.len,ss.end_offset)/60) *
> 60
> )/86400 end_dttm
> ,ss.sell_id
> ,so.type_id
> ,p.prg_id
> ,sog.group_title
> ,1 gabriel_airing_ct
> ,ss.bgn_offset
> ,ss.end_offset
> from s_sch_off ss
> ,s_obj so
> ,s_obj_group sog
> ,ge_net_broadcast_time nettm
> ,ps_air_byte psab
> ,ge_calendar_qtrs q
> ,p_programs p
> where ss.s_week between q.start_dt and q.end_dt
> and q.year_num = in_year -- BN !!3
> and q.qtr_num = in_qtr -- BN !!3
> and q.cal_id = 1
> and ss.sell_sch_id = in_netId
> and so.type_id = in_typeId
> and psab.air_byte = ss.air_byte
> and ss.sell_sch_id = nettm.net_id
> and ss.s_week between nettm.valid_begin_dt and
> nettm.valid_end_dt
> and ss.prg_id = p.prg_id
> and ss.sell_id = so.sell_id
> and so.s_obj_group_id = sog.s_obj_group_id
> order by ss.s_week+psab.day,ss.s_offset,ss.bgn_offset;
> lr_lastRow lc_GabrielAiring%rowtype;
> ln_holeCount number := 0;
> lb_first boolean := true;
> kn_telecastType constant number := 2;
> kn_daypartType constant number := 4;
> type ltn_table is table of number index by binary_integer;
> ltn_sellTypeIds ltn_table;
> type ltc_table is table of varchar(50) index by binary_integer;
> ltc_SellTypeName ltc_table;
> ln_iType number;
> ln_pgmId number;
> ln_ShouldHaveSellingTitleCount number;
> ProgramShouldHaveSellingTitle boolean := true;
>
> lnStartOfDay number;
> lnEndOfDay number;
>
> procedure RecordHole( ir_row lc_GabrielAiring%rowtype, in_prgId
> number, ir_lastRow lc_GabrielAiring%rowtype, ic_type varchar2 ) is
> lc_insertText varchar2(20);
> ln_firstTime number;
> ln_lastTime number;
> begin
> if ic_type = 'H' then
> lc_insertText := 'Hole ';
> ln_firstTime := ir_lastRow.start_dttm - ir_row.bday;
> ln_lastTime := ir_lastRow.end_dttm - ir_row.bday;
> else
> lc_insertText := 'Ovlp ';
> ln_firstTime := ir_lastRow.start_dttm - ir_row.bday;
> ln_lastTime := ir_lastRow.end_dttm - ir_row.bday;
> end if;
>
> RecordScheduleAnomoly(ir_row.bday
> ,ir_lastRow.start_dttm - ir_row.bday
> ,ir_lastRow.end_dttm - ir_row.bday
> ,null
> ,ir_lastRow.sell_id
> ,in_prgId
> ,lc_insertText
> ||'('
> ||ir_lastRow.group_title
> ||' ends at '
> ||to_char( ir_lastRow.end_dttm, 'hh24:mi' )
> ||')'
> ,' ('
> ||ir_row.group_title
> ||' starts at '
> ||to_char( ir_row.start_dttm, 'hh24:mi' )
> ||').'
> );
> ln_holeCount := ln_holeCount + 1;
> end;
>
> begin
> dbms_output.put_line( chr(9) );
> dbms_output.put_line( 'Gabriel Selling Schedule Holes' );
> dbms_output.put_line( chr(9) );
> ltn_SellTypeIds(1) := kn_telecastType;
> ltc_SellTypeName(1) := 'Title Specific Sell Ids ("2" type)';
> ltn_sellTypeIds(2) := kn_daypartType;
> ltc_SellTypeName(2) := 'Daypart Sell Ids ("4" type)';
> lr_lastRow.bday := to_date('1-jan-1900','dd-mon-yyyy');
>
> -- DKN has a 6AM start of broadcast day, but they don't schedule
> anything until 10AM or after 1PM
> if in_netId = 95
> then
> lnStartOfDay := 14400;
> lnEndOfDay := 25200;
> else
> lnStartOfDay := 0;
> lnEndOfDay := 86400;
> end if;
>
> <<l_types>>
> for ln_iType in 1..2 loop
> dbms_output.put_line( chr(9) );
> dbms_output.put_line( chr(9) );
> dbms_output.put_line( ltc_SellTypeName( ln_iType ) );
> dbms_output.put_line( chr(9) );
> -- PrintScheduleAnomolyHdg;
> dbms_output.put_line( '. PrgId/ Program Title/'
> );
> dbms_output.put_line( 'Date TC id SID Problem
> Description' );
> dbms_output.put_line( '-------- ------- -------
> ---------------------------------------------------------' );
> <<l_checkGabrielForHoles>>
> for lr_GabrielAiring in
> lc_GabrielAiring(ltn_sellTypeIds(ln_iType))
> loop
> if lr_lastRow.bday != lr_GabrielAiring.bday then
> if lr_GabrielAiring.offset != lnStartOfDay then -- On day
> change, should have something at start of day
> RecordHole( lr_GabrielAiring, lr_GabrielAiring.prg_id,
> lr_lastRow, 'H' );
> end if;
> if lr_lastRow.offset + lr_lastRow.len != lnEndOfDay then
> -- On day change, should have something at end of previous day
> RecordHole (lr_lastRow, lr_lastRow.prg_id, lr_lastRow,
> 'H' );
> end if;
> else
> if lr_lastRow.end_dttm > lr_GabrielAiring.start_dttm then
> RecordHole( lr_GabrielAiring,
> lr_GabrielAiring.prg_id,lr_lastRow, 'O' );
> elsif lr_lastRow.end_dttm < lr_GabrielAiring.start_dttm
> then
> -- Find all of the programs in the schedule between this
> time slot
> -- and the end of the last time slot
>
> ln_ShouldHaveSellingTitleCount := 0;
>
>
> for crec in (select ps.prg_id
> from p_sch_off ps,p_programs p
> where prg_sch_id = in_netId
> and prg_sched_dt = lr_GabrielAiring.bday
> and ps.prg_id = p.prg_id
> --and ((lr_lastRow.end_offset > 0
> -- and prg_offset >=
> lr_lastRow.offset)
> -- or (lr_lastRow.end_offset = 0
> -- and prg_offset >
> lr_lastRow.offset))
> and prg_offset >= lr_lastRow.offset
> and prg_offset <
> lr_GabrielAiring.offset)
>
>
> loop
> ln_pgmId := crec.prg_id;
>
> for rc_findProgram in (select prg_id from dci_prg_with_no_sid
> where prg_id = lr_GabrielAiring.prg_Id ) loop
> ProgramShouldHaveSellingTitle := false;
> end loop;
>
> if ProgramShouldHaveSellingTitle = true
> then
> ln_ShouldHaveSellingTitleCount :=
> ln_ShouldHaveSellingTitleCount + 1;
> end if;
> end loop;
> if ln_ShouldHaveSellingTitleCount > 0
> then
> RecordHole( lr_GabrielAiring, ln_pgmId, lr_lastRow,
> 'H' );
> end if;
> end if;
> end if;
> lr_lastRow := lr_GabrielAiring;
> end loop l_checkGabrielForHoles;
> end loop l_types;
> dbms_output.put_line( chr(9) );
> dbms_output.put_line( ln_holeCount || ' hole(s) in Gabriel schedule
> found.' );
> end Check_For_Gabriel_Holes;
>
> Please help or
> advice.
> Thanks,
>
> Joe

[Quoted] Looking at your code there are a number of things I could take issue with such as why you are using a cursor loop rather than a bulk collection.

But given that you've provided no version number and no error message how do you expect anyone to know what isn't working?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org