title: Using PL/SQL Cursor to return a result set for reporting tool
body: <p>I'm a report writer.  My queries are generally SELECT statements from some source (in this case Oracle Exadata) that Selects from one or more tables, joins, filters in WHERE, further filters groups in HAVING, etc.  I generally have READ only rights to whatever source I'm connecting to.  So, I can't create sprocs, packages, or functions.</p>
  
  <p>I have a need to use advanced functionality on a complex query, using FOR..LOOPs, IF THEN ELSE structures, etc. to refine a derived result-set for output to a reporting tool such as Qlikview or Tableau.</p>
  
  <p>As I'm learning as I go, I've found many lessons on using cursors in complex logic structures but every lesson uses dbms_output.put_line as the result of each iteration.  The output ends up in the buffer, instead of the Result Set.  I've learned that a SYS_REFCURSOR is a pointer to a result set.  Sound promising but all the examples start with CREATE OR REPLACE PROCEDURE/PACKAGE.</p>
  
  <p>There are gaps in my understanding I'm hoping you can fill if I explain what my desired outcome is:</p>
  
  <p>I have a complex query that synthesizes multiple SELECT statements by UNION'ing them, uses sub-selects in Join and Where clauses, etc.  It returns a nearly complete Result Set but I have need to further refine it before sending the Result Set to the reporting tool.</p>
  
  <p>I can: </p>
  
  <pre><code>DECLARE
    CURSOR current_Schedule IS
    SELECT --insert complex query here-- ;
  
    row_Schedule  cur_Schedule%ROWTYPE;
  
  BEGIN
    IF NOT current_Schedule%ISOPEN THEN
      OPEN current_Schedule;
    END IF;
  
    LOOP
      FETCH current_Schedule INTO row_Schedule;
      EXIT WHEN current_Schedule%NOTFOUND;
      --Here's where I think my question is....
      dbms_output.put_line('row_Schedule.Login ' || row_Schedule.Code)
    END LOOP;
  
  EXCEPTION --put exception clause here
  
  END;
  </code></pre>
  
  <p>This works and validates the concept that I can iterate through the results of the complex query, update, filter, etc. but the problem is....</p>
  
  <p>It outputs to the Buffer, not the Result Set.</p>
  
  <p>It's likely I'm mis-understanding some PL/SQL concepts.  All of the research examples I've found here and other sources assume the user has the ability to create Functions, Packages, etc.  </p>
  
  <p>I'm limited to launching this as a script from a QlikView ODBC connection with Read Only rights to the source.  </p>
  
  <p>Is there a solution given my limitations?</p>
  
  <p>Thank you for taking the time to read this and much gratitude if you can provide a coherent answer.</p>
  
  <p>mfc</p>
  
  <hr>
  
  <p>EDIT: Additional Information</p>
  
  <p>I'm working with a Work Force Management Schema, de-normalized and housed in an Exadata data-lake.  I have 2 tables DET_SEG (Detailed Segment) and GEN_SEG (General Segment).  DET_SEG contains fk's to Employee and Segment Code tables, NOM_DATE, START MOMENT and STOP MOMENT.  Codes are of 2 types, Additive and Subtractive. Additive are Work Codes indicating business units employees are assigned to, Subtractive codes are any type of code that takes an employee from work such as Break, Lunch, Sick, etc.  All codes are tied to a table that generates Time Intervals at 30 minutes.  As code could occupy a complete or partial interval, and as they can span multiple intervals, I ended up with 6 unique Use Cases to solve for: Code that start before, occupy an entire and end in subsequent Interval is one.  Codes that start before, end within is another.
  You can imagine the rest, I won't iterate through them.  The final output generates a computed column titled "Minutes" indicating the amount of time the Additive or Subtractive Code occupied in the Interval.</p>
  
  <p>I ended up creating 6 unique SELECT blocks UNION'ed together to output the penultimate solution.  Here's an example of one of the shorter SELECT's:</p>
  
  <pre><code>SELECT 
    cal.GlobalIntervals AS G_Intervals,
    mainstart.startmoment AS StartMoment,
    mainstart.stopmoment AS StopMoment,
    mainstart.code as Code,
    mainstart.ID AS Login,
    ((case mainstart.code
          when 'BRK1' then round(24*60*(mainstart.startmoment -mainstart.stopmoment),0)
          when 'BRK2' then round(24*60*(mainstart.startmoment - mainstart.stopmoment),0)
          when 'BRK3' then round(24*60*(mainstart.startmoment - mainstart.stopmoment),0)
          else round(24*60*(mainstart.startmoment - mainstart.stopmoment),0)
    end)) as Minutes  
  from
  (
  SELECT
      (
      to_date(to_char((
      SELECT CLNDR_DT
      FROM COMMOBJ.DIM_DATE
      where CLNDR_DT = trunc(sysdate)
      ), 'dd-mon-yyyy') || HOUR_24_LABEL_CD, 'dd-mon-yyyy hh24:mi:ss')
      ) as GlobalIntervals
  FROM    COMMOBJ.DIM_TIME t
  WHERE   second_nbr = 0
  AND     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') as StartMoment,
      New_time(to_date(30121899,'ddMMyyyy')+EWFM.DET_SEG.STOP_MOMENT/1440,'GMT','PST') as StopMoment,
      SEG_CODE.CODE,
      SEG_CODE.DESCR,
      EMP.ID,
      EMP.LAST_NAME,
      EMP.FIRST_NAME 
  FROM 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)
      WHERE trunc (New_time(to_date(30121899,'ddMMyyyy')+EWFM.DET_SEG.START_MOMENT/1440,'GMT','PST')) =  trunc(sysdate)  
      and EWFM.SEG_CODE.CRNT_REC_IND = 'Y'
      and 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 &lt; mainstart.StartMoment 
    --StopMoment occurs on Next Interval 
    and (cal.GlobalIntervals + (1/24/60 * 30)) = mainstart.StopMoment 
    --StartMoment occurs prior to Next Interval
    and mainstart.StartMoment &lt; (cal.GlobalIntervals + (1/24/60 * 30))
  </code></pre>
  
  <p>The output looks like this:</p>
  
  <p>G_INTERVALS   | STARTMOMENT   | STOPMOMENT    | CODE | LOGIN  | MINUTES<br>
  4/17/17 12:00  | 2/17/17 12:00  | 2/17/17 12:15  | BRK1  | ABC123  | -15<br>
  4/17/17 12:00  | 2/17/17 12:00  | 2/17/17 08:00  | SHIFT | XYZ321  |  30<br>
  4/17/17 12:30  | 2/17/17 12:45  | 2/17/17 01:45  | LUNCH | LK4567  | -15  </p>
  
  <p>The Case statement for the Minutes column changes for each Use Case as does the ON condition in the final Join.  Although perhaps not the most efficient, it generates a dataset that is almost ready for a <strong>Shrinkage Report</strong>.</p>
  
  <p>The final condition (and the reason for this post) is the GEN_SEG table.  It contains information for Day Long events such as Planned Sick (SICKPL), unplanned Sick (SICKUP), etc.  It only has a date, no START or STOP moments.  This is where I was looking for further refinement.  Either using a Cursor to iterate through the Result-Set and for every Login found in the GEN_SEG, and For every value &lt;0, change it to 0 in order to zero out the negative values associated with the Subtractive Codes.  </p>
  
  <p>Ultimately we want to discover Shrinkage within a workforce.  The employee schedule is only the first step.  Once the Employee Schedule with planned and unplanned shrinkage has been refined, I'll bring in the Actuals for further refinement.</p>
  
  <p>I suspect I've written a short novel  but I hope I've included just enough detail to explain the need for a secondary tier of refinement.  If you have any ideas or critique, I'm grateful.</p>
  
  <p>Cheers~!</p>