oracle - Using PL/SQL Cursor to return a result set for reporting tool -

i'm report writer. queries select statements source (in case oracle exadata) selects 1 or more tables, joins, filters in where, further filters groups in having, etc. have read rights whatever source i'm connecting to. so, can't create sprocs, packages, or functions.

i have need use advanced functionality on complex query, using for..loops, if else structures, etc. refine derived result-set output reporting tool such qlikview or tableau.

as i'm learning go, i've found many lessons on using cursors in complex logic structures every lesson uses dbms_output.put_line result of each iteration. output ends in buffer, instead of result set. i've learned sys_refcursor pointer result set. sound promising examples start create or replace procedure/package.

there gaps in understanding i'm hoping can fill if explain desired outcome is:

i have complex query synthesizes multiple select statements union'ing them, uses sub-selects in join , clauses, etc. returns complete result set have need further refine before sending result set reporting tool.

i can:

declare   cursor current_schedule   select --insert complex query here-- ;    row_schedule  cur_schedule%rowtype;  begin   if not current_schedule%isopen     open current_schedule;   end if;    loop     fetch current_schedule row_schedule;     exit when current_schedule%notfound;     --here's think question is....     dbms_output.put_line('row_schedule.login ' || row_schedule.code)   end loop;  exception --put exception clause here  end; 

this works , validates concept can iterate through results of complex query, update, filter, etc. problem is....

it outputs buffer, not result set.

it's i'm mis-understanding pl/sql concepts. of research examples i've found here , other sources assume user has ability create functions, packages, etc.

i'm limited launching script qlikview odbc connection read rights source.

is there solution given limitations?

thank taking time read , gratitude if can provide coherent answer.

mfc

---

edit: additional information

i'm working work force management schema, de-normalized , housed in exadata data-lake. have 2 tables det_seg (detailed segment) , gen_seg (general segment). det_seg contains fk's employee , segment code tables, nom_date, start moment , stop moment. codes of 2 types, additive , subtractive. additive work codes indicating business units employees assigned to, subtractive codes type of code takes employee work such break, lunch, sick, etc. codes tied table generates time intervals @ 30 minutes. code occupy complete or partial interval, , can span multiple intervals, ended 6 unique use cases solve for: code start before, occupy entire , end in subsequent interval one. codes start before, end within another. can imagine rest, won't iterate through them. final output generates computed column titled "minutes" indicating amount of time additive or subtractive code occupied in interval.

i ended creating 6 unique select blocks union'ed output penultimate solution. here's example of 1 of shorter select's:

select    cal.globalintervals g_intervals,   mainstart.startmoment startmoment,   mainstart.stopmoment stopmoment,   mainstart.code code,   mainstart.id login,   ((case mainstart.code         when 'brk1' round(24*60*(mainstart.startmoment -mainstart.stopmoment),0)         when 'brk2' round(24*60*(mainstart.startmoment - mainstart.stopmoment),0)         when 'brk3' round(24*60*(mainstart.startmoment - mainstart.stopmoment),0)         else round(24*60*(mainstart.startmoment - mainstart.stopmoment),0)   end)) minutes   ( select     (     to_date(to_char((     select clndr_dt     commobj.dim_date     clndr_dt = trunc(sysdate)     ), 'dd-mon-yyyy') || hour_24_label_cd, 'dd-mon-yyyy hh24:mi:ss')     ) globalintervals    commobj.dim_time t   second_nbr = 0 ,     mod(minute_nbr, 30) = 0  ) cal left outer join (   select distinct         new_time(to_date(30121899,'ddmmyyyy')+ewfm.det_seg.start_moment/1440,'gmt','pst') startmoment,     new_time(to_date(30121899,'ddmmyyyy')+ewfm.det_seg.stop_moment/1440,'gmt','pst') stopmoment,     seg_code.code,     seg_code.descr,     emp.id,     emp.last_name,     emp.first_name  ewfm.det_seg det_seg     inner join ewfm.seg_code seg_code on (det_seg.seg_code_sk = seg_code.seg_code_sk)     inner join ewfm.emp emp on (det_seg.emp_sk = emp.emp_sk)     inner join sbcg_adhoc.sbcg_staff sbcg_staff on (emp.id = sbcg_staff.agent_login)     trunc (new_time(to_date(30121899,'ddmmyyyy')+ewfm.det_seg.start_moment/1440,'gmt','pst')) =  trunc(sysdate)       , ewfm.seg_code.crnt_rec_ind = 'y'     , seg_code.code not in ('shift', 'ccbout', 'holida', 'lind', 'pcht', 'pind', 'pxtr', 'lcht', 'lxtr', 'psvc', 'hv saves inbound' )   )mainstart   --startmoment occurs after current interval   on  cal.globalintervals < mainstart.startmoment    --stopmoment occurs on next interval    , (cal.globalintervals + (1/24/60 * 30)) = mainstart.stopmoment    --startmoment occurs prior next interval   , mainstart.startmoment < (cal.globalintervals + (1/24/60 * 30)) 

the output looks this:

g_intervals | startmoment | stopmoment | code | login | minutes
4/17/17 12:00 | 2/17/17 12:00 | 2/17/17 12:15 | brk1 | abc123 | -15
4/17/17 12:00 | 2/17/17 12:00 | 2/17/17 08:00 | shift | xyz321 | 30
4/17/17 12:30 | 2/17/17 12:45 | 2/17/17 01:45 | lunch | lk4567 | -15

the case statement minutes column changes each use case on condition in final join. although perhaps not efficient, generates dataset ready shrinkage report.

the final condition (and reason post) gen_seg table. contains information day long events such planned sick (sickpl), unplanned sick (sickup), etc. has date, no start or stop moments. looking further refinement. either using cursor iterate through result-set , every login found in gen_seg, , every value <0, change 0 in order 0 out negative values associated subtractive codes.

ultimately want discover shrinkage within workforce. employee schedule first step. once employee schedule planned , unplanned shrinkage has been refined, i'll bring in actuals further refinement.

i suspect i've written short novel hope i've included enough detail explain need secondary tier of refinement. if have ideas or critique, i'm grateful.

cheers~!

in pl/sql block consuming cursor, there nothing return client.

you return cursor anonymous block if on oracle 12c (see implicit result sets), sql no procedural control.

probably you'd want pipelined function gives in effect procedural view. however, can't read-only account need create function , collection type returns.

you can lot sql though. i'd interested know reporting requirement needs procedural logic.