This post is part 6 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive the series, please see part 1.

All the Series Parts

Published

Part 1 – write less configuration with SQL*Loader Express Mode
Part 2 – write less application code with Identity Columns
Part 3 – write less application code with In-Database Archiving
Part 4 – write less application code with Temporal Validity
Part 5 – write less code in SQL with Row Limiting
Part 6 – write less “inappropriately located” code with SELECT FROM Package-Level Collection Types
Part 7 – write less code in SQL with Lateral Inline Views
Part 8 – write less “inappropriately located” code with PL/SQL in the WITH Clause

Task #6

We need to write a stored procedure that gets a collection parameter of “project updates”, each one with PROJECT_ID, UPDATE_TIME and STATUS, and updates the PROJECTS table with the latest status of each project.
For example, the input parameter may look like this:

PROJECT ID	UPDATE TIME	STATUS
1	10:00	2
1	14:00	3
2	09:00	1
3	09:00	4
2	08:00	3
3	08:00	2
2	10:00	2

The highlighted lines are the lines we are interested in – the latest one for each project.
For project_id 1 the line with update_time 14:00 is the latest, so we need to update its status to 3.
For project_id 2 the line with update_time 10:00 is the latest, so we need to update its status to 2.
For project_id 3 the line with update_time 09:00 is the latest, so we need to update its status to 4.
The input parameter in this example does not contain information about other projects, so only the projects with PROJECT_ID 1, 2 and 3 should be updated.

Let’s start with the API. A nice solution would be to define the procedure and the necessary types in one package, like this:

  create or replace package projects_dl as 

    type proj_update_t is record(
      project_id  projects.project_id%type,
      update_time date,
      status      projects.status%type);

    type proj_update_tt is table of proj_update_t;

    procedure update_status(
        i_proj_update_list in proj_update_tt 
    );

  end projects_dl;

 

select project_id,
       max(status) keep(dense_rank last order by update_time) latest_status
from table(i_proj_update_list)
group by project_id;

Note: I used here the LAST function in order to sort each group by one column (UPDATE_TIME) and get the value from another column (STATUS). If you want to learn more about this function (and its twin – the FIRST function), I wrote about it here.

Now we can use this query as the driving result set for a MERGE statement that updates the PROJECTS table:

      merge into projects p
      using ( select project_id,
                     max(status) keep(dense_rank last 
                       order by update_time) latest_status
              from table(i_proj_update_list)
              group by project_id ) i 
      on (p.project_id = i.project_id)
      when matched then
        update set p.status=i.latest_status;

Note: if you want to learn more about the MERGE statement, I wrote about it here.

And finally, we can use this MERGE statement as the body of our procedure:

  create or replace package body projects_dl as 

    procedure update_status(
      i_proj_update_list in proj_update_tt) is 
    begin 

      merge into projects p
      using ( select project_id,
                     max(status) keep(dense_rank last 
                       order by update_time) latest_status
              from table(i_proj_update_list)
              group by project_id ) i 
      on (p.project_id = i.project_id)
      when matched then
        update set p.status=i.latest_status;

    end update_status;
  end projects_dl;

This solution works nicely in Oracle 12c, but not in earlier versions. In 12c it is possible to select from package-level collection types. If we try to compile this package in 11g it will fail with the following error:

PL/SQL: SQL Statement ignored
ORA-22905: cannot access rows from a non-nested table item
PLS-00642: local collection types not allowed
           in SQL statements

A Pre-12c Solution

To achieve the same functionality in 11g we had to create the proj_update_t and proj_update_tt types in the schema level, rather than in the projects_dl package, although they are used only in the scope of this package:

CREATE OR REPLACE TYPE proj_update_t AS OBJECT (
    project_id  INTEGER,
    update_time DATE,
    status      INTEGER);
/

CREATE TYPE proj_update_tt AS TABLE OF proj_update_t;
/

CREATE OR REPLACE PACKAGE projects_dl AS 
    PROCEDURE update_status(
        i_proj_update_list IN proj_update_tt 
    );
END projects_dl;
/

Conclusion

We saw in this part of the Write Less with More series that “SELECT FROM Package-Level Collection Types” allows us to write less “inappropriately-located” code.
The next post will be about another new Oracle 12c feature – Lateral Inline Views.