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
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
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|
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;
And now to the implementation: we can do it procedurally, but take a look at the input parameter – it looks like a table that we need to find some rows in it. It will be much nicer to use SQL and treat the collection parameter as a table.
Let’s start with a SQL query that uses the TABLE expression to un-nest the collection and finds the latest status for each project:
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; /
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.