What is ListAGG?

This new LISTAGG built-in feature was released in Oracle 11g Release 2 a function that will enable us to perform string aggregation naively.  String aggregation is been popular technique and different methods prior to Oracle 11g.

The LISTAGG function has the following syntax

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

• the column or expression to be aggregated;
• the WITHIN GROUP keywords;
• the ORDER BY clause within the grouping.

What is string aggregation?

String aggregation is the grouping and concatenation of rows of data into a single row per group. For example:

DEPTNO ENAME

——— ———-

10 CLARK

10 KING

10 MILLER

20 ADAMS

20 FORD

20 JONES

Using the Emp table from the scott user:

SELECT deptno

,LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

FROM  emp

GROUP  BY deptno;

DEPTNO EMPLOYEES

——— ————————-

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES

We can see that the employee names have been grouped and concatenated into a single per group.

LISTAGG as an analytic function

As with many aggregate functions, LISTAGG can be converted to an analytic function by adding the OVER() clause. The following example demonstrates the analytic equivalent of our previous example (for each department, aggregate employee names in hire date order).

SQL> SELECT deptno

2  ,      ename

3  ,      hiredate

4  ,      LISTAGG(ename, ‘,’)

5            WITHIN GROUP (ORDER BY hiredate)

6            OVER (PARTITION BY deptno) AS employees

7  FROM   emp;

 

DEPTNO ENAME      HIREDATE    EMPLOYEES

———- ———- ———– ————————————-

10 CLARK      09/06/1981  CLARK,KING,MILLER

10 KING       17/11/1981  CLARK,KING,MILLER

10 MILLER     23/01/1982  CLARK,KING,MILLER

20 SMITH      17/12/1980  SMITH,JONES,FORD,SCOTT,ADAMS

20 JONES      02/04/1981  SMITH,JONES,FORD,SCOTT,ADAMS

20 FORD       03/12/1981  SMITH,JONES,FORD,SCOTT,ADAMS

20 SCOTT      19/04/1987  SMITH,JONES,FORD,SCOTT,ADAMS

20 ADAMS      23/05/1987  SMITH,JONES,FORD,SCOTT,ADAMS

30 ALLEN      20/02/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

30 WARD       22/02/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

30 BLAKE      01/05/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

30 TURNER     08/09/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

30 MARTIN     28/09/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

30 JAMES      03/12/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

Prior to Oracle 11g Rel 2 – STRAGG and WM_CONCAT

LISTAGG is similar to WM_CONCAT and STRAGG, but the difference been that it is a documented and supported function by oracle. This function is a part of Oracle 11g Release 2 version.

Additional features of this function have over previous functions are:

1) Can select your own separator. The default value is NULL

2) Can get the ordered list by ordering the column

3) Supported by Oracle, can be used in Production system

4) Much faster than WM_CONCAT and little better than STRAGG function

I had a project building a process that uses the LISTAGG function in a generic way.  Real Example:

The process needed to create a list of values based on the Column_Name from the table provided to be used in dynamic sql to return correct records based on criteria and list of values provided. The columns had a naming convention of SEQ in name to allow its additional columns sequences needed for new markets. The codes snipped below are simplified to help illustrate the contents of the discussion of using the LISTAGG.

The code snipped below retrieved the Column Names for the string in the dynamic sql:

SELECT REPLACE (LISTAGG (‘NVL(‘ || COLUMN_NAME || ‘,0)’, ‘,’)

WITHIN GROUP (ORDER BY COLUMN_NAME),

‘,NVL’,’||”,”||NVL’) AS markets

INTO V_MARKET_LISTS

FROM ALL_TAB_COLS

WHERE table_name = ‘QUOTE_DATA’

AND OWNER = ‘SCHEMA’

AND column_name LIKE ‘%SEQ%’

ORDER BY COLUMN_NAME;

The snippet query to retrieve values using the v_market_list provide from above query:

v_work_market :=

‘SELECT ‘|| v_markets_list || ‘

from QUOTE_DATA qm

where QM.SYMBOL in ‘’’ || P_SYMBOL || ‘’’;

— Fetch all the market sequence for timeframe.

OPEN cur_qm_seq_ref FOR v_work_markets;

FETCH cur_qm_seq_ref INTO v_market_seq_list;

CLOSE cur_qm_seq_ref;

Execute this query to return the comma delimited values to retrieve correct records to filter and returned.

Here’s the code snipped to retrieve the quote center code rows using the dynamic query.

v_qm_market :=

‘(SELECT quote_center_code

from QUOTE_DATA qm

where QM.QUOTE_SEQ in ( ‘|| v_MARKET_SEQ_LISTS ||’)’;

Conclusion:

In summary is that LISTAGG is here to stay and is documented and support by Oracle. Other option were more workarounds and not recommended for future use.  I have found the LISTAGG to be a good string aggregation technique that all developers to be aware of for the right situation. Clearly the performance is faster for the new LISTAGG function over previous techniques used before.

Avoiding Mutating Tables

Avoiding Mutating Tables
Ok, so you’ve just received the error:

ORA-04091: table XXXX is mutating, trigger/function may not see it
Avoiding the mutating table error is fairly easy. We must defer processing against the mutating or constraining table until an AFTER trigger. We will consider two cases:

· Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values
· Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the :OLD values

There is two cases when you want to use :NEW or :OLD values:

Case 1 – you only need to access the :NEW values
This case is the simplest. What we will do is capture the ROWIDS of the inserted or updated rows. We can then use these ROWIDS in an AFTER trigger to query up the affected rows.

It always takes 3 triggers to work around the mutating table error. They are:

· A before trigger to set the package state to a known, consistent state
· An after, row level trigger to capture each rows changes
· An after statement trigger to actually process the change.

Case 2 – you need to access the :OLD values
This one is a little more involved but the concept is the same. We’ll save the actual OLD values in an array (as opposed to just the rowids of the new rows).

· A before trigger to set the package state to a known, consistent state
· An after, row level trigger to capture each rows changes
· An after statement trigger to actually process the change.

Currently, implemented in DMS for Deriving Giving Status, but it will be a good example for future reference in Avoiding Mutating Tables trigger need to be written in DMS.

Here is the implementation:

This package spec is used to maintain our state. We will save the rowids of newly inserted / updated rows in this package. We declare 2 arrays – one will hold our new rows rowids (newRows). The other is used to reset this array, it is an empty array. We also have defined an array of record with the 3 main datatypes Number, Varchar2, and Date to store the old values used in capturing data for delete or update triggers.

CREATE OR REPLACE PACKAGE CMN_PKG IS

— Define the record to be used in Avoiding Mutating Tables.
TYPE DELETE_LIST_REC IS RECORD
(V_ID NUMBER, — Save any ids
V_NAME VARCHAR2(100), — Save any Strings
V_DATE DATE); — Save any Date

TYPE rowidArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
TYPE array IS TABLE OF DELETE_LIST_REC INDEX BY BINARY_INTEGER;

oldvals array;
oldempty array;
newRows rowidArray;
empty rowidArray;

..
END;

I put the arrays in the CMN_PKG, so that it could be reuse in other triggers that may use in DMS. My solution for deriving giving status for CASE 1 and CASE 2.

CASE 1:
We must set the state of the above package to some known, consistent state before we beign processing the row triggers. This trigger is mandatory, we *cannot* reply on the AFTER trigger to reset the package state. This is because during a multi-row insert or update, the ROW trigger may fire but the AFTER trigger does not have to fire – if the second row in an update fails due to some constraint error – the row trigger will have fired 2 times but the AFTER trigger (which we relied on the reset the package ) will never fire. That would leave 2 erroneous rowids in the newRows array for the next insert/update to see. Therefore, before the insert / update takes place, we ‘reset’.

CREATE OR REPLACE TRIGGER CAMPAIGN.DNR_SUMMARY_TRG_BIS
BEFORE INSERT
ON CAMPAIGN.DNR_SUMMARY
DECLARE

P_GIVING_STATUS NUMBER;
BEGIN
— Used to reset array.
cmn_pkg.newRows := cmn_pkg.empty;
END;

This trigger simply captures the rowed of the affected row and saves it in the newRows array.

CREATE OR REPLACE TRIGGER CAMPAIGN.DNR_SUMMARY_TRG_AIR
AFTER INSERT
ON CAMPAIGN.DNR_SUMMARY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

P_GIVING_STATUS NUMBER;
BEGIN
cmn_pkg.newRows( cmn_pkg.newRows.count+1 ) := :new.rowid;
END;

This trigger processes the new rows. We simply loop over the newRows array processing each newly inserted/modified row in turn. This trigger derives the Giving Status and update the Donor accounts based on the new rows.

CREATE OR REPLACE TRIGGER CAMPAIGN.DNR_SUMMARY_TRG_AIS
AFTER INSERT
ON CAMPAIGN.DNR_SUMMARY
DECLARE
P_GIVING_STATUS NUMBER;
v_donor_id NUMBER;

BEGIN
P_GIVING_STATUS := 0;

FOR i IN 1 .. cmn_pkg.newRows.count LOOP

select donor_id
into v_donor_id
from DNR_SUMMARY
where rowid = cmn_pkg.newRows(i);

P_GIVING_STATUS := DNR_PKG.GET_GIVING_STATUS(v_donor_id);

— Update the DNR_ACCOUNTS giving status on Donor accounts
— hat is not override.
BEGIN
UPDATE DNR_ACCOUNTS
SET GIVING_STATUS_ID = P_GIVING_STATUS
WHERE DONOR_ID = v_donor_id
AND GIVING_STATUS_FL = ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN

— Consider logging the error and then re-raise

…
END;

CASE 2:
The trigger reset the arrays. Explain more in Case 1.

CREATE OR REPLACE TRIGGER CAMPAIGN.DNR_SUMMARY_TRG_BDS
BEFORE DELETE OR UPDATE
ON CAMPAIGN.DNR_SUMMARY

BEGIN
— Used to reset array.
cmn_pkg.oldvals := cmn_pkg.oldempty;
END;

In case 2, instead of capturing the rowed, we must capture the before image of the row. We cannot really undo the delete here; we are just capturing the deleted data.

CREATE OR REPLACE TRIGGER CAMPAIGN.DNR_SUMMARY_TRG_BDR
BEFORE DELETE OR UPDATE
ON CAMPAIGN.DNR_SUMMARY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

BEGIN
— Store the Donor ID to be used later.
cmn_pkg.oldvals(cmn_pkg.oldvals.count+1).V_ID := :OLD.DONOR_ID;
END;

This trigger will process all delete and update rows affected to determine the giving status for the affected donor account.

CREATE OR REPLACE TRIGGER CAMPAIGN.DNR_SUMMARY_TRG_ADS
AFTER DELETE OR UPDATE
ON CAMPAIGN.DNR_SUMMARY
DECLARE
v_donor_id NUMBER;
P_GIVING_STATUS NUMBER;
BEGIN

FOR i IN 1 .. cmn_pkg.oldvals.count LOOP

v_donor_id := cmn_pkg.oldvals(i).V_ID;
P_GIVING_STATUS := DNR_PKG.GET_GIVING_STATUS(v_donor_id);

–Update the DNR_ACCOUNTS giving status on Donor accounts
–that is not override.

BEGIN
UPDATE DNR_ACCOUNTS
SET GIVING_STATUS_ID = P_GIVING_STATUS
WHERE DONOR_ID = v_donor_id
AND GIVING_STATUS_FL = ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
— Consider logging the error and then re-raise
…
END;

Thanks to Tom Kyte article on Avoid Mutating Tables I was able to incorporate to solve my problem and provide an example that can be reused to solve other mutating trigger to be written for the DMS system in the future.

To Developers using invoker’s and definer’s

Most of our procedures, functions, and packages are definer’s rights. But, in our development with reusable code share amongst different modules and different schema user. We should be using invoker’s right to benefit all of us on the team. I’ll try to briefly explain the two rights:

“Definer rights” means that when you run a stored program, it runs under the authority of the definer or owner of the program.

“Invoker rights” means that when a user runs a stored program, it runs under the authority of that user or invoker.

The concept is key here on when to use it or not. I have apply it to package that our more generic and reuse across all our application. The implementation is easy as this example will show:

CREATE OR REPLACE FUNCTION countemp RETURN NUMBER
AUTHID CURRENT_USER IS
retval NUMBER;
BEGIN
SELECT count(*) INTO retval FROM emp;
RETURN retval;
END;
/

Now, you maybe asking how does this benefit in my development environment and how does this effect me. Well, as a developer I have always encouraged development under my own schema before moving it to our application schema owner. This is a good practice to avoid all developer to develop all the time in the application schema owner.

Definitions of what these package do is beyond this email explanation.

The purpose of invoke right is allow reuse as well as put misc objects under the application schema when we are first development a new module.

This is not a whitepaper, so I’m sure there are still many questions not answer in this email. The are others source to refer to like otn, metalink, and Oracle8i and higher books.

Just try to keep this concept in mind during you development when it applies.

Refactoring for PL/SQL Developers

I will highlight an article on a practices that we should try to maintain in our coding practice.

What is refactoring?
Most PL/SQL developers stay very busy building applications. As a result, relatively few explore the wider world of software programming, in which the topic of refactoring is known.

Refactoring, to quote the book Refactoring: Improving the Design of Existing Code by Martin Fowler et al. (Addison-Wesley, 1999), “is the process of changing a software system in such a way that it does not alter the external behavior of the code yet improves its internal structure. . . . It is a disciplined way to clean up code that minimizes the chances of introducing bugs. In essence when you refactor you are improving the design of the code after it has been written.”

Who Needs Refactoring?
All professional software developers, in fact, need to refactor their code. That’s not bad news; it’s good news, as I will demonstrate in this article. Additional good news: Many of us already do refactor our code. We are constantly going back into existing programs—written by ourselves or by someone else—to fix a problem, enhance the program to match new requirements, make it run faster, upgrade it to use new PL/SQL features, and more.

With each of these steps, we often—but not always—improve the internal design of our code. So why introduce a new buzzword to describe what we are already doing? Refactoring goes beyond simply making recommendations for best practices; the discipline of refactoring offers step-by-step guides to making very specific improvements. These guides help us make the changes in a consistent manner, without introducing new bugs.

An Imperfect Program—Like All Others

The your code isn’t perfect, but there’s nothing very surprising about that.
There isn’t any such thing as a perfect program. The question is how
to make it better. Given the imperfections described above, I suggest
that the following refactorings would apply very well to your code:

Create local initialization and cleanup procedures.
Include an exception section in every block of code, or justify its absence.
Replace all hard-coded literal values with named constants.
Hide complex expressions and logic behind a function or variable.
Construct each loop so that there is only one way to exit.

The Power of Refactoring
I have found the idea and discipline of refactoring to be very helpful. Refactoring takes us a step beyond identifying best practices by also providing a step-by-step approach to making changes, to minimize the chance of introducing bugs.

Here’s the full article if you want more details about Refactoring:

http://www.oracle.com/technology/oramag/oracle/05-jan/o15plsql.html

New functions for Selecting Nth MAX or MIN

These function will allow you to select the Nth Max or Min from the column. It can be handy to finding the Nth Max or Min when developing applications.

The new functions and parameters are:

FUNCTION GET_MAX_NUM(P_TABLE IN VARCHAR2, P_COLUMN IN VARCHAR2,
P_POSITION IN NUMBER,P_WHERE_CLAUSE IN VARCHAR2 DEFAULT NULL)

FUNCTION GET_MIN_NUM(P_TABLE IN VARCHAR2, P_COLUMN IN VARCHAR2,
P_POSITION IN NUMBER,P_WHERE_CLAUSE IN VARCHAR2 DEFAULT NULL)

Here’s an example I wrote to use the functions to show you how it can be used.

To get the Max and Min for the Column.

declare
p_val number;
p_str VARCHAR2(50) := ‘Column = ”value”’;
begin
p_val := cmn_pkg.get_max_num(”,”,2,p_str); dbms_output.put_line(‘fetch Max p_val ‘ || to_char(p_val));
p_val := cmn_pkg.get_min_num(”,”,2,p_str); dbms_output.put_line(‘fetch Min p_val ‘ || to_char(p_val));

end;

Output:

fetch Max p_val 2005
fetch Min p_val 1987

New Function to return description from ID

The purpose of this function is to lookup description and return value based on ID or Code passed too it.

PACKAGE CMN_PKG:

FUNCTION GET_CODE_DESCRIPTION(
p_code_column VARCHAR2, — The Column ID field.
p_description_column VARCHAR2, — The Column for the description name.
p_table_name VARCHAR2, — The Table name.
p_code_value NUMBER) — The ID or Code to lookup description.
RETURN VARCHAR2;

How to Use the Procedure

The method for using this procedure is to pass the table and column names to this procedure. For example, to look up a department number from the DEPARTMENTS table:

:field_on_the_screen := cmn_pkg.get_code_description(‘DEPARTMENT_ID’, ‘DEPARTMENT_NAME’, ‘DEPARTMENTS’, :value_to_look_up);

If you want to look up a concatenation of two columns, such as last name and first name, use the following type of call:

:field_on_the_screen := cmn_pkg.get_code_description(‘EMPLOYEE_ID’,
‘FIRST_NAME || ” ” || LAST_NAME’, ‘EMPLOYEES’, :value_to_look_up);

Kaitech’s Tech Blog

Welcome to my Oracle Tech Blog. I hope everyone will benefit from my tech blog is a source for sharing Oracle info from my experiences and projects to benefit my clients and hopefully the user community.

Quote of the day: “You can either follow your fears or be led by your passions.”

Kai Liu – Oracle Professional

email: kaitechinc@gmail.com
http://www.linkedin.com/in/kaitech