Oracle PL/SQL Tutorial #15 Part 1 -

Interaction of Multi-Level PL/SQL

Collections with Oracle

This series of Oracle PL/SQL tutorials is designed to give you a good grounding in PL/SQL so that you can make better, more efficient, more complex applications and spend less time doing so. See here for previous articles. This tutorial continues the exploration of the interaction of your collections with the Oracle database.

Populating Multi-

Level PL/SQL

Collections From

Oracle

We said in a previous module that PL/SQL is not always the best tool for the job. We can extend that to say that any particular feature in PL/SQL may not be the best one to use. In other words, keep things as simple as possible.  Multi-level collections are a case in point. You may find it simpler to have several one dimensional collections rather than one multi dimensional collection. Let’s look at an example to demonstrate this. The HR schema of the sample Oracle database contains tables holding information about employees and their job history and ancillary tables to support the normalised data model. For the purposes of this example we’ll ignore the ancillary tables (departments, countries, regions, locations) and just look at employees and their job history. To replicate these tables in PL/SQL we could have one multi-dimensional collection, two separate collections  or one collection of records. The last option is the easiest so we’ll start with that.

Populating a PL/SQL Collection of Records From Oracle

The first thing we need to is to decide what details about each employee we’ll retrieve from the database as that will determine our record structure. Let’s say that we want the employee id, the employee’s first and last names concatenated, and the start date, end date and title of each job (including the current) that they’ve had with the company. That gives us a query something like this: SELECT employee_id emp_id       ,first_name||' '||last_name emp_name       ,start_date       ,end_date       ,jobs.job_id       ,job_title FROM employees JOIN job_history USING (employee_id) JOIN jobs ON (job_history.job_id = jobs.job_id); If we declare that SQL statement as a cursor, we can then define our collection as being of the row type of the cursor. Rather then repeat it assume that we’ve declared the SQL statement as a cursor called emp_hist. See PL/SQL tutorial #5 to learn more about declaring and using explicit cursors. You will also notice that the column job_id is prefixed with the table name (jobs). This is because job_id is an attribute of employee - defining the current job held by the employee - as well as job and job history. Obviously we want the ids of all the jobs that an employee has held not just the current one. Here is our collection type declaration: TYPE emp_tab IS TABLE OF emp_hist%ROWTYPE INDEX BY PLS_INTEGER; Next we declare the collection and index variables:  emp emp_tab; i   PLS_INTEGER:= 1; With those definitions in place we can now write the code to populate our PL/SQL table from Oracle using a cursor for loop as follows:    for rec in emp_hist loop        emp(i) := rec;        i := i + 1;    end loop; As each element of our PL/SQL collection is of the same type as the row returned by the cursor, we can assign the whole row to an element of the collection in one go. We started filling the collection at cell #1 but we could have started at any number (positive or negative) within the range of valid binary integers. This approach has the effect of flattening (de-normalized) job_history which may or may not be a disadvantage in your application.

Populating Multiple PL/SQL Collections From One Query

Another way way of avoiding the use of multi-level collections is to have separate single-dimensional collections - one for each table in the Oracle database that we’re reading. In this case we’re reading three tables - employees. job_history and jobs - so we obviously need three collections. Right? Well, maybe not. All we’re returning from the jobs table is the job title, which we can denormalize (in our PL/SQL application) so that it’s an attribute of job_history replacing job_id. That means we only need two collections - one for employee details and another for each employee’s job history. That does not mean, however that we need 2 separate queries - we can use the same query as before but just store the results in the appropriate collection. As we’re separating the query results into two PL/SQL collections we need to define a record type on which the job history collection will be based. TYPE emp_job_hist_rec IS RECORD (job_id    NUMBER, job_title  VARCHAR2(35), start_date DATE, end_date   DATE); Here are the collection type declarations: TYPE emp_tab IS TABLE OF VARCHAR2(46) INDEX BY PLS_INTEGER; TYPE emp_job_hist_tab IS TABLE OF emp_job_hist_rec INDEX BY PLS_INTEGER; Next we declare the collection and index variables:  emp emp_tab; emp_job_hist emp_job_hist_tab; With those definitions in place we can now write the code to populate our PL/SQL collections from Oracle using a cursor for loop as follows:    for rec in emp_hist loop        emp(rec.emp_id) := rec.emp_name;        emp_job_hist(rec.emp_id).start_date := rec.start_date;        emp_job_hist(rec.emp_id).end_date   := rec.end_date;        emp_job_hist(rec.emp_id).job_title  := rec.job_title;        emp_job_hist(rec.emp_id).job_id     := rec.job_id;    end loop; This time we’ve used the employee id as the index into the collections. Even though this is defined as NUMBER in the database, we can use it as the index as Oracle will perform an implicit conversion to PLS_INTEGER. Part 2 of PL/SQL tutorial #15 will actually demonstrate populating a multi-level collection and how to write changes back to Oracle. PL/SQL tutorial #14- read/write collections from/to Oracle          PL/SQL tutorial #15 Part 2 (coming soon)  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England  Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.      View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2015. All rights reserved.