StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest way to encapsulate complex Oracle PL/SQL cursor logic as a view?
text
Body
copied!<p>I've written PL/SQL code to denormalize a table into a much-easer-to-query form. The code uses a temporary table to do some of its work, merging some rows from the original table together.</p> <p>The logic is written as a <a href="http://www.oreillynet.com/lpt/a/3136" rel="nofollow noreferrer">pipelined table function</a>, following the pattern from the linked article. The table function uses a <code>PRAGMA AUTONOMOUS_TRANSACTION</code> declaration to permit the temporary table manipulation, and also accepts a cursor input parameter to restrict the denormalization to certain ID values.</p> <p>I then created a view to query the table function, passing in all possible ID values as a cursor (other uses of the function will be more restrictive).</p> <p>My question: is this all really necessary? Have I completely missed a much more simple way of accomplishing the same thing?</p> <p>Every time I touch PL/SQL I get the impression that I'm typing way too much.</p> <p><strong>Update:</strong> I'll add a sketch of the table I'm dealing with to give everyone an idea of the denormalization that I'm talking about. The table stores a history of employee jobs, each with an activation row, and (possibly) a termination row. It's possible for an employee to have multiple simultaneous jobs, as well as the same job over and over again in non-contiguous date ranges. For example:</p> <pre><code>| EMP_ID | JOB_ID | STATUS | EFF_DATE | other columns... | 1 | 10 | A | 10-JAN-2008 | | 2 | 11 | A | 13-JAN-2008 | | 1 | 12 | A | 20-JAN-2008 | | 2 | 11 | T | 01-FEB-2008 | | 1 | 10 | T | 02-FEB-2008 | | 2 | 11 | A | 20-FEB-2008 | </code></pre> <p>Querying that to figure out who is working when in what job is non-trivial. So, my denormalization function populates the temporary table with just the date ranges for each job, for any <code>EMP_ID</code>s passed in though the cursor. Passing in <code>EMP_ID</code>s 1 and 2 would produce the following:</p> <pre><code>| EMP_ID | JOB_ID | START_DATE | END_DATE | | 1 | 10 | 10-JAN-2008 | 02-FEB-2008 | | 2 | 11 | 13-JAN-2008 | 01-FEB-2008 | | 1 | 12 | 20-JAN-2008 | | | 2 | 11 | 20-FEB-2008 | | </code></pre> <p>(<code>END_DATE</code> allows <code>NULL</code>s for jobs that don't have a predetermined termination date.)</p> <p>As you can imagine, this denormalized form is much, much easier to query, but creating it--so far as I can tell--requires a temporary table to store the intermediate results (e.g., job records for which the activation row has been found, but not the termination...yet). Using the pipelined table function to populate the temporary table and then return its rows is the only way I've figured out how to do it.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload