John kCripe
Member
yeah, like i said, most all of the plsql code debugs just great though i've got some more complicated sql being opened (in cursor) that the debugger gets stuck in...i'm wondering how can i i get the debugger to either skip around this code (or just enable the debugger to handle it)?
i'll paste the sql in here i'm opening (in stored proc within package btw using open cursor): (so what think, i know i could cram all this sql code into a function to insert into a table, run the function outside of the package to create my data..then comment out the function so i could debug but thats a lot of screwing around to enable debugging, must be better way)??
CURSOR sis_cur IS
WITH
CTE_GET_DATA AS
(select pregrad.*--, 'Y' comp_input
from msu_sis_interface_comp_input pregrad
where 'Y'=v_comp_input
union all
select sis.*--, 'N' comp_input
from msu_sis_interface_input sis
where 'N'=v_comp_input ),
CTE_SIS_DATA as
(select si.pid, si.soc_sec_nbr,
nvl(mdrs.unique_rs_list, si.record_source) unique_recsrc,
si.award_seq_id,
si.student_level_code, si.award_term_seq_id,
si.intended_sesn_code, si.intended_year_date,
si.coll_code, si.dept_code, si.major_code,
si.grad_date,
case when si.degree_type_code='D.O.' then 'DO' else msu_nvl(upper(si.degree_type_code)) end degree_type_code,
si.prgrm_prps_code,
si.awd_hon_coll_maj_flag,
si.award_stat_code, si.award_cnfrd_flag, si.award_honor_code,
si.student_name, si.student_name_last, si.student_name_first, si.student_name_middle,
si.name_title, si.full_name,
case when persuf.suffix_code is null then ' ' else persuf.suffix_code end pers_suffix,
msu_nvl(si.name_suffix) pers_suffix_raw,
si.gender_flag, si.birth_date,
case when ms.MARITAL_STATUS_CODE is null then 'S' else ms.marital_status_code end marital_status,
msu_nvl(upper(si.marital_status)) marital_status_raw,
case when race.ethnic_code is null then ' ' else race.ethnic_code end ethnic_code,
msu_nvl(upper(si.ethnic_code)) ethnic_code_raw,
si.restrict_phone, si.restrict_addr,
si.citizen_type, si.citizen_flag,
case when msu_nvl(si.addr_country_code) in ('US',' ') then 'N' else 'Y' end foreign_cntry,
msu_nvl(si.addr_country_code) addr_country_code,
case when trim(si.addr_line1) is null then v_noaddress_lit else msu_nvl(si.addr_line1) end addr_line1,
si.addr_line2, si.addr_line3, si.addr_line4, si.addr_city_name, si.addr_state_code, si.addr_zip_code,
si.addr_ph_area_code, si.addr_ph_number, si.addr_phone_ext,
si.addr_type_code,
si.prim_major, si.pilot_email, si.restrict_email,
si.deceased_flag,
si.restrict_all, si.restrict_degree,
si.athlete_sport, si.record_source,
case when si.record_source in ('A','I','F') then 'A'
when si.record_source in ('N') then ' '
else si.record_source end non_grad_code
FROM CTE_GET_DATA si
left join tms_marital_status ms on msu_nvl(upper(si.marital_status))=ms.marital_status_code
left join tms_suffix persuf on msu_nvl(upper(si.name_suffix))=persuf.suffix_code
left join tms_race race on msu_nvl(upper(si.ethnic_code))=race.ethnic_code
left join ( --list of unique record_source in input file for pid
select distinct si.pid,
listagg(si.record_source, ',')
within group (order by si.pid, si.record_source) over (partition by si.pid) unique_rs_list
from CTE_GET_DATA si
where exists (select * from CTE_GET_DATA sis
where sis.pid=si.pid and sis.record_sourcesi.record_source)
group by si.pid, si.record_source ) mdrs on si.pid=mdrs.pid
),
CTE_SIS_1 AS
(
select sis.*,
case when sis.addr_country_code='US' then ' ' else msu_nvl(ctry.short_desc) end country_text,
msu_nvl(profsuf.prof_suffix_code) prof_suffix,
MSUB025_IS_MSU_DEGREE(sis.degree_type_code,sis.student_level_code, sis.non_grad_code) is_real_degree
from CTE_SIS_DATA sis
left join tms_country ctry on sis.addr_country_code=ctry.country_code
left join tms_prof_suffix profsuf on sis.degree_type_code=profsuf.prof_suffix_code
) ----end of with
--BEGIN SELECT
select sis.*,
rt.profsufx_list,
rt.degree_real_ct, rt.degree_cert_ct, rt.degree_stu_ct, rt.ALL_REAL_DEGREES_DM,
' ' entity_record_type_code --entity.record_type_code (loaded from entity later in pgm)
from CTE_SIS_1 sis
left join ( --get some metedata on the collection of degrees for each student
select pid,
sum(degree_real_ct) degree_real_ct,
sum(degree_cert_ct) degree_cert_ct,
sum(degree_stu_ct) degree_stu_ct,
case when sum(degree_real_ct)>0 and sum(degree_real_ct)=sum(degree_dm_ct) then 'Y' else 'N' end ALL_REAL_DEGREES_DM,
max(profsufx_list) profsufx_list
from (
select sg.pid,
case when sg.is_real_degree='1' then 1 else 0 end degree_real_ct,
case when trim(sg.non_grad_code) is null and sg.is_real_degree='0' then 1 else 0 end degree_cert_ct,
case when trim(sg.non_grad_code) in ('A','G','S') then 1 else 0 end degree_stu_ct,
case when sg.is_real_degree='1' and sg.coll_code='95' then 1 else 0 end degree_dm_ct,
listagg(trim(prof_suffix), ',') within group (order by rownum) over (partition by pid) profsufx_list
from CTE_SIS_1 sg
)
group by pid
) rt on sis.pid=rt.pid
where rt.pid='A04280402'
order by sis.pid, sis.award_term_seq_id, sis.student_level_code, sis.award_seq_id;
i'll paste the sql in here i'm opening (in stored proc within package btw using open cursor): (so what think, i know i could cram all this sql code into a function to insert into a table, run the function outside of the package to create my data..then comment out the function so i could debug but thats a lot of screwing around to enable debugging, must be better way)??
CURSOR sis_cur IS
WITH
CTE_GET_DATA AS
(select pregrad.*--, 'Y' comp_input
from msu_sis_interface_comp_input pregrad
where 'Y'=v_comp_input
union all
select sis.*--, 'N' comp_input
from msu_sis_interface_input sis
where 'N'=v_comp_input ),
CTE_SIS_DATA as
(select si.pid, si.soc_sec_nbr,
nvl(mdrs.unique_rs_list, si.record_source) unique_recsrc,
si.award_seq_id,
si.student_level_code, si.award_term_seq_id,
si.intended_sesn_code, si.intended_year_date,
si.coll_code, si.dept_code, si.major_code,
si.grad_date,
case when si.degree_type_code='D.O.' then 'DO' else msu_nvl(upper(si.degree_type_code)) end degree_type_code,
si.prgrm_prps_code,
si.awd_hon_coll_maj_flag,
si.award_stat_code, si.award_cnfrd_flag, si.award_honor_code,
si.student_name, si.student_name_last, si.student_name_first, si.student_name_middle,
si.name_title, si.full_name,
case when persuf.suffix_code is null then ' ' else persuf.suffix_code end pers_suffix,
msu_nvl(si.name_suffix) pers_suffix_raw,
si.gender_flag, si.birth_date,
case when ms.MARITAL_STATUS_CODE is null then 'S' else ms.marital_status_code end marital_status,
msu_nvl(upper(si.marital_status)) marital_status_raw,
case when race.ethnic_code is null then ' ' else race.ethnic_code end ethnic_code,
msu_nvl(upper(si.ethnic_code)) ethnic_code_raw,
si.restrict_phone, si.restrict_addr,
si.citizen_type, si.citizen_flag,
case when msu_nvl(si.addr_country_code) in ('US',' ') then 'N' else 'Y' end foreign_cntry,
msu_nvl(si.addr_country_code) addr_country_code,
case when trim(si.addr_line1) is null then v_noaddress_lit else msu_nvl(si.addr_line1) end addr_line1,
si.addr_line2, si.addr_line3, si.addr_line4, si.addr_city_name, si.addr_state_code, si.addr_zip_code,
si.addr_ph_area_code, si.addr_ph_number, si.addr_phone_ext,
si.addr_type_code,
si.prim_major, si.pilot_email, si.restrict_email,
si.deceased_flag,
si.restrict_all, si.restrict_degree,
si.athlete_sport, si.record_source,
case when si.record_source in ('A','I','F') then 'A'
when si.record_source in ('N') then ' '
else si.record_source end non_grad_code
FROM CTE_GET_DATA si
left join tms_marital_status ms on msu_nvl(upper(si.marital_status))=ms.marital_status_code
left join tms_suffix persuf on msu_nvl(upper(si.name_suffix))=persuf.suffix_code
left join tms_race race on msu_nvl(upper(si.ethnic_code))=race.ethnic_code
left join ( --list of unique record_source in input file for pid
select distinct si.pid,
listagg(si.record_source, ',')
within group (order by si.pid, si.record_source) over (partition by si.pid) unique_rs_list
from CTE_GET_DATA si
where exists (select * from CTE_GET_DATA sis
where sis.pid=si.pid and sis.record_sourcesi.record_source)
group by si.pid, si.record_source ) mdrs on si.pid=mdrs.pid
),
CTE_SIS_1 AS
(
select sis.*,
case when sis.addr_country_code='US' then ' ' else msu_nvl(ctry.short_desc) end country_text,
msu_nvl(profsuf.prof_suffix_code) prof_suffix,
MSUB025_IS_MSU_DEGREE(sis.degree_type_code,sis.student_level_code, sis.non_grad_code) is_real_degree
from CTE_SIS_DATA sis
left join tms_country ctry on sis.addr_country_code=ctry.country_code
left join tms_prof_suffix profsuf on sis.degree_type_code=profsuf.prof_suffix_code
) ----end of with
--BEGIN SELECT
select sis.*,
rt.profsufx_list,
rt.degree_real_ct, rt.degree_cert_ct, rt.degree_stu_ct, rt.ALL_REAL_DEGREES_DM,
' ' entity_record_type_code --entity.record_type_code (loaded from entity later in pgm)
from CTE_SIS_1 sis
left join ( --get some metedata on the collection of degrees for each student
select pid,
sum(degree_real_ct) degree_real_ct,
sum(degree_cert_ct) degree_cert_ct,
sum(degree_stu_ct) degree_stu_ct,
case when sum(degree_real_ct)>0 and sum(degree_real_ct)=sum(degree_dm_ct) then 'Y' else 'N' end ALL_REAL_DEGREES_DM,
max(profsufx_list) profsufx_list
from (
select sg.pid,
case when sg.is_real_degree='1' then 1 else 0 end degree_real_ct,
case when trim(sg.non_grad_code) is null and sg.is_real_degree='0' then 1 else 0 end degree_cert_ct,
case when trim(sg.non_grad_code) in ('A','G','S') then 1 else 0 end degree_stu_ct,
case when sg.is_real_degree='1' and sg.coll_code='95' then 1 else 0 end degree_dm_ct,
listagg(trim(prof_suffix), ',') within group (order by rownum) over (partition by pid) profsufx_list
from CTE_SIS_1 sg
)
group by pid
) rt on sis.pid=rt.pid
where rt.pid='A04280402'
order by sis.pid, sis.award_term_seq_id, sis.student_level_code, sis.award_seq_id;
