Oracle LISTAGG Function Example

Today I read about LISTAGG aggregate / analytic function in Oracle, and thought would write a short blog about this, explaining various methods of using LISTAGG with suitable examples. I’m going to approach this tutorial with really simple examples so that you can understand LISTAGG completely. The tutorial assumes you know some basics of SQL, using aggregate functions and analytic functions. You don’t need any understanding on LISTAGG.  The different steps involved in this example are summarized below:

Step by Step Approach to Learn LISTAGG SQL Function
Step by Step Approach to Learn LISTAGG SQL Function

1. LISTAGG Introduction


In Oracle documentation terms, LISTAGG for a specified measure, orders data within each group specified by ORDER BY, and then concatenates the values of the measure column. !!! Sounds Greek and Latin? Don’t worry, we will slowly break this definition into examples that you can understand very easily.  At this stage, you should be knowing that LISTAGG helps you to combine values across columns, with a specified delimiter / condition and allows some ordering options in the process. This much of definition would be helpful for you to get started with LISTAGG. For a detailed syntax diagram of LISTAGG for Oracle nerds, you can refer this link. The syntax of LISTAGG is provided below:

LISTAGG(measure_expr [, 'delimiter'])
  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

We will now approach this syntax with a set of examples, and you will understand this in no time.


2.Simple LISTAGG Example


We now create a simple students table with grade and student name as columns. We will also pump in some test data for the basic LISTAGG example.

CREATE TABLE STUDENTS
(
GRADE VARCHAR2(20),
ST_NAME VARCHAR2(50)
)

INSERT INTO STUDENTS VALUES ('A','JOHN')
INSERT INTO STUDENTS VALUES ('A','JOE')
INSERT INTO STUDENTS VALUES ('A','JOAKIM')
INSERT INTO STUDENTS VALUES ('B','BROAD')
INSERT INTO STUDENTS VALUES ('C','KEVIN')
INSERT INTO STUDENTS VALUES ('C','SEEHO')
INSERT INTO STUDENTS VALUES ('D','AIDAN')

SELECT * FROM STUDENTS

A screen dump of the table is shown below:

LISTAGG- Simple Example - Input Table Screenshot
LISTAGG- Simple Example - Input Table Screenshot
LISTAGG SQL example to separate all names (measure) that belong to Grade A, through a “,” (delimiter) is shown below:

SELECT LISTAGG(ST_NAME,',') WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS WHERE GRADE='A'

As expected, the output is a single row of data, as shown below

JOHN,JOE,JOAKIM

Congratulations, you have written your first LISTAGG example. If you note the SQL above, we have sorted the names in descending order by using DESC keyword. The default is “Ascending” order.  How can you get the result grouped by grades? To do this, you include a GRADE column in your SELECT clause, and add a GROUP BY to the end. This is shown below:


SELECT GRADE,LISTAGG(ST_NAME,',') WITHIN GROUP(ORDER BY ST_NAME) FROM STUDENTS GROUP BY GRADE

The output is shown in the screenshot below:

LISTAGG - Group set aggregate - example output
LISTAGG - Group set aggregate - example output
We achieved the grouping of names at a GRADE level, by using GROUP BY clause in the SQL. This explains the usage of LISTAGG as a GROUP SET AGGREGATE function. The LISTAGG function has returned the result for every group defined in the GROUP BY clause. We will now see some advanced examples of LISTAGG.

3. Combining columns in LISTAGG Output


It is possible to combine more than one column in the LISTAGG output. To illustrate this, we will add one more column to the table and update it with some data as shown below:

/* Add a new column to the table */
ALTER TABLE STUDENTS ADD (ST_LAST_NAME VARCHAR2(50))
/* set the column value to M across all rows */
UPDATE STUDENTS SET ST_LAST_NAME='M'

To combine ST_NAME and ST_LAST_NAME as an example, you run a LISTAGG SQL as shown below:

SELECT GRADE,LISTAGG(ST_NAME||'-'||ST_LAST_NAME,';') WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS GROUP BY GRADE

The output is shown below:

Combining more than one column in LISTAGG - Example Output
Combining more than one column in LISTAGG - Example Output
As you can see, we have combined both the “name” columns with a ‘;’ separator, grouped by GRADE.


4. Handling NULLS in LISTAGG


You can control if columns with NULL values should appear first or last, by using NULLS FIRST / NULLS LAST inside the ORDER BY clause. Default value is NULLS LAST. To explain this, we will insert a row into the table with a NULL value on last name as shown below:


INSERT INTO STUDENTS VALUES('A','MURRAY',NULL)

Here are two SQL statements that handle NULLS differently in LISTAGG. The output is presented side by side for a comparison.

SELECT 'nulls first',GRADE,LISTAGG(ST_NAME||'-'||ST_LAST_NAME,';') WITHIN GROUP(ORDER BY ST_LAST_NAME NULLS FIRST ) FROM STUDENTS WHERE GRADE='A' GROUP BY GRADE
union all
SELECT 'nulls last',GRADE,LISTAGG(ST_NAME||'-'||ST_LAST_NAME,';') WITHIN GROUP(ORDER BY ST_LAST_NAME ) FROM STUDENTS WHERE GRADE='A' GROUP BY GRADE


Note: I have given a UNION ALL to get the output in a single window so that I can grab a screenshot. This is for illustrative purposes only.

Handling NULLS in LISTAGG Usage
Handling NULLS in LISTAGG Usage


As you can see from above, we got MURRAY as first data as we wanted NULLS to come first in the output. It is just the opposite in the second row. 

5. LISTAGG as Reporting Aggregate


We will now discuss how to use LISTAGG as an analytic function in Oracle. To explain this, we will add a SCORE column to the students table and update it with some random number using DBMS_RANDOM package. We will also add a SUB_GRADE column to illustrate the usage better. So, be ready to modify your test table a bit now and use the SQLs below for that:


ALTER TABLE STUDENTS ADD(SCORE NUMBER)

UPDATE STUDENTS SET SCORE=TRUNC(DBMS_RANDOM.VALUE(1,100))

ALTER TABLE STUDENTS ADD (SUB_GRADE VARCHAR2(10))

UPDATE STUDENTS SET SUB_GRADE='A1' WHERE GRADE='A' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='A2' WHERE GRADE='A' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='B1' WHERE GRADE='B' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='C1' WHERE GRADE='C' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='D1' WHERE GRADE='D' AND ROWNUM <3 AND SUB_GRADE IS NULL


Here is a screen dump of the modified table to test analytic examples on LISTAGG:

LISTAGG As Analytic Function - Setup Table
LISTAGG As Analytic Function - Setup Table
Now, if you want to know the MIN score at a Grade and Sub grade level, you run a query as shown below:

SELECT GRADE,SUB_GRADE,MIN(SCORE) FROM STUDENTS GROUP BY GRADE,SUB_GRADE

Now, if you want to LISTAGG all MIN Scores at a GRADE and sub grade level, you use LISTAGG with a PARTITION class, that partitions on Grade.  An example SQL is shown below:

SELECT GRADE,
       SUB_GRADE,
       MIN(SCORE),
       LISTAGG(MIN(SCORE), ',') WITHIN GROUP(ORDER BY GRADE) OVER(PARTITION BY GRADE)S
  FROM STUDENTS
 GROUP BY GRADE, SUB_GRADE

This query produces an output as shown below: (as we are using DBMS_RANDOM earlier, you may get different results for this query )

LISTAGG - Analytic Function Output
LISTAGG - Analytic Function Output
You can try different examples of using analytic functions with LISTAGG on the same lines and post your queries if any.


6.LISTAGG without Delimiter


As per the syntax of LISTAGG, the delimiter is optional and is defaulted to NULL if you don’t specify it. So, the SQL

SELECT GRADE,
       SUB_GRADE,
       MIN(SCORE),
       LISTAGG(MIN(SCORE)) WITHIN GROUP(ORDER BY GRADE) OVER(PARTITION BY GRADE)S
  FROM STUDENTS
 GROUP BY GRADE, SUB_GRADE

Returns the same output as in (5), but there will not be a delimiter separating 24 and 28 (column 4 in the output). It would rather be a NULL delimited output as shown below:

LISTAGG- NULL / NO Delimiter - Example Output
LISTAGG- NULL / NO Delimiter - Example Output

7.Using Columns as Delimiters in LISTAGG


You can use another column name as a delimiter in LISTAGG. However, the column name should be  a part of the SQL query, otherwise you would get an error. For example, if you run this SQL;

SELECT GRADE,LISTAGG(ST_NAME,SUB_GRADE) WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS GROUP BY GRADE
---ORA-30497: Argument should be a constant or a function of expressions in GROUP BY

You get the ORA error as shown above, as the column SUB_GRADE is not in the select list. Replacing this with GRADE would get rid of ORA 30497 error, and will give an output that uses the value in GRADE column as a delimiter. However, it is a bad idea to use a column value as a delimiter unless you have an absolute requirement for this.(sometimes it affects the readability of your output)


8.Using PL SQL Function as Delimiter in LISTAGG


You can use a simple PL SQL function as a delimiter in LISTAGG. As an example, consider the function below:

create or replace function f(grade in varchar2)  
RETURN VARCHAR2 IS
delimiter varchar2(2);
BEGIN
if grade='A' then
  delimiter:=':';
else
  delimiter:=';';
end IF;
return delimiter;

end f;

This function returns the delimiter as ‘:’ if the GRADE is A. It returns ‘;’ in all other cases. You can invoke this function via SQL in LISTAGG delimiter clause. This is shown below:

SELECT GRADE,LISTAGG(ST_NAME,f(GRADE)) WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS GROUP BY GRADE

The output of this SQL is shown below:

Using a function as delimiter in LISTAGG
Using a function as delimiter in LISTAGG


As you can infer from the output, Row with Grade A has ‘:’ as delimiter. However, Row # C has ‘;’ as delimiter.

That completes a breezy tutorial on Oracle LISTAGG function. Hope the examples we have provided helped you to understand LISTAGG functionality, and you are now geared to use this function in your day today requirements powerfully. You can post any questions that you have around LISTAGG in the comments section of this blog. Happy LISTAGGing.

Create MD5 Hash for File in PeopleCode - Example

In this tutorial, we will explain how to create a MD5 hash / Checksum for a file in PeopleSoft, with a PeopleCode example.  We will utilise java.security.MessageDigest to generate the MD5 hash. You can easily stick this code to an Application Engine program or even a record field event, depending on your requirements. The high level steps involved in creating a MD5 hash are captured below:

Steps to Create a MD5 Hash / Checksum for a file in PeopleCode
Steps to Create a MD5 Hash / Checksum for a file in PeopleCode


We will take you through all these steps now with PeopleCode snippets.


1. Read Input File


In this step, we will read the input file for which you have to generate a MD5 hash, as an object of type java.io.FileInputStream in PeopleCode. You have to pass the file name as a parameter to this call as shown below:

/* Create an InputStream object for the file first */
Local JavaObject &InputMD5Reader = CreateJavaObject("java.io.FileInputStream", "c:\temp\TESTER.zip");


2. Create Message Digest Object


To get an object of type java.security.MessageDigest, you have to invoke the getInstance static method and pass “MD5” as the hash type to this method. We use GetJavaClass PeopleCode method for this purpose, and the code to get a MessageDigest object is shown below:

/* Get a Message Digest Object */
Local JavaObject &messageDigest = GetJavaClass("java.security.MessageDigest").getInstance("MD5");


3. Read Bytes from File


To generate an MD5 hash, it is required to read all the bytes from a File into a Java Array recursively, and pass the bytes that are read to a function that can generate the digest. We will use the code snippet below to read the input file byte by byte in PeopleCode.

/* Create Java Array to assist in Hashing */
Local JavaObject &ByteBufferArray = CreateJavaArray("byte[]", 1024);
/* Read Input File Byte by Byte */
Local number &NumberOfBytes = &InputMD5Reader.read(&ByteBufferArray);
While &NumberOfBytes > 0   
   &NumberOfBytes = &InputMD5Reader.read(&ByteBufferArray);
End-While;


4. Update Message Digest Object


For every set of bytes that is read, we invoke the update method in MessageDigest class, by passing the byte array, offset and number of bytes read. This method updates the digest using the specified array of bytes, by starting at the offset. The signature of this method is shown below:

Signature of update method in MessageDigest Class
Signature of update method in MessageDigest Class
The Peoplecode to invoke this method is shown below:

   &messageDigest.update(&ByteBufferArray, 0, &NumberOfBytes);


5.Get MD5 Checksum As Byte Array


When you pass all the bytes to the update method, the digest gets updated iteratively. Now, we have to get the calculated MD5 digest back in a byte array. To do this, we invoke the digest method in MessageDigest class. This method completes the hash computation by performing final operations  like padding etc. PeopleCode snippet is shown below

/* Get the calculated digest back */
Local JavaObject &bytearray = &messageDigest.digest();


6.Encode MD5 Hash as Hex String


Now that you have got the byte array in PeopleCode, you can encode this to a Hex string to create the MD5 hash. You can use any preferred approach of yours to create the hex string. The easiest one is to use the Hex class defined in org.apache.commons.codec.binary.Hex and invoke the encodeHexString method by passing the byte array you got in step 5. This returns a MD5 string back directly. But to use this, you need to load the JAR file commons-codec-1.4.jar (or equivalent, depending on your Java version) defined by Apache Commons Codec library. You can alternatively use native Java components, but you may have to use reflection based approaches for this. I leave the choice to you.

Local string &encodehex = GetJavaClass("org.apache.commons.codec.binary.Hex").encodeHexString(&bytearray);

You can use the returned MD5 hash string inside your program suitably. You can even wrap this as a PeopleCode function by passing the file name and returning a string back


Create MD5 Checksum For File – Complete PeopleCode Program


The complete PeopleCode program to generate MD5 checksum for a file is shown below

/* Calculate MD5 Checksum for a file in PeopleCode */
/* Create an InputStream object for the file first */
Local JavaObject &InputMD5Reader = CreateJavaObject("java.io.FileInputStream", "c:\temp\TESTER.zip");
/* Get a Message Digest Object */
Local JavaObject &messageDigest = GetJavaClass("java.security.MessageDigest").getInstance("MD5");
/* Create Java Array to assist in Hashing */
Local JavaObject &ByteBufferArray = CreateJavaArray("byte[]", 1024);
/* Read Input File Byte by Byte */
Local number &NumberOfBytes = &InputMD5Reader.read(&ByteBufferArray);
While &NumberOfBytes > 0   
   &messageDigest.update(&ByteBufferArray, 0, &NumberOfBytes);
   &NumberOfBytes = &InputMD5Reader.read(&ByteBufferArray);
End-While;
/* Get the calculated digest back */
Local JavaObject &bytearray = &messageDigest.digest();
/* Get MD5 hash as string */
Local string &encodehex = GetJavaClass("org.apache.commons.codec.binary.Hex").encodeHexString(&bytearray);

That completes our tutorial to generate MD5 hash / checksum for a File in PeopleSoft using PeopleCode. You can use this example in your code segment, and post a comment for any clarifications. 

Read / Extract ZIP Files - PeopleCode Example

In this tutorial, we will discuss how to Read / extract a ZIP file in PeopleSoft with a PeopleCode example. If you are looking to ZIP a file in PeopleCode, you should refer to our earlier tutorials. We will be using java.util.zip.ZipInputStream to read a ZIP file in PeopleSoft, and then extract every entry inside the ZIP file to a file on disk using the methods available in Java, extended to PeopleCode. The high level steps to extract a ZIP file in PeopleCode are documented below: 

Steps to Extract ZIP Files in PeopleCode
Steps to Extract ZIP Files in PeopleCode

The ZIP File we want to read and extract is shown in the screenshot below:

Sample ZIP Input File to Extract in PeopleCode
Sample ZIP Input File to Extract in PeopleCode

The output we are looking for when we extract this file, should be like the following as shown below:

C:\temp\dir1 ------- /* Create Directory in PeopleCode */
C:\temp\dir2 ------- /* Create Directory in PeopleCode */
C:\temp\dir1\source.log ----- /* Extract File into the directory */
C:\temp\dir2\source.sql ----- /* Extract File into the directory */


We have to create two directories in PeopleCode and place the input files into this directory, all in a generic fashion. Let us now get started with this tutorial.

1. Read ZIP File as ZipInputStream


In this step, we create a JavaObject in PeopleCode that will read the input ZIP file as an object of type java.util.zip.ZipInputStream . In order to create a ZipInputStream, you have to pass an object of type InputStream as a parameter. So, we create a FileInputStream object that will point to the physical zip file on the disk, and pass this to the code. The PeopleCode snippet is shown below:

Local JavaObject &InputZipReader = CreateJavaObject("java.util.zip.ZipInputStream", CreateJavaObject("java.io.FileInputStream", "c:\temp\TESTER.zip"));


2. Read First ZIP Entry


There is no direct method available in ZipInputStream to read the number of entries in the ZIP file. So, we read the first entry using getNextEntry method, which returns a ZipEntry back to us. ZipEntry is defined in java.util.zip.ZipEntry, if you are keen to read as a reference.

Local JavaObject &EntryForZipFile = &InputZipReader.getNextEntry();

3. Extract Directories in PeopleCode


Now, when you extract a ZIP file in PeopleCode, you need to find a way to know if the extracted entry is a directory. If it is a directory, then you need to create a directory in the file system, so that you can put the files into the right folder. You use isDirectory method in ZipEntry to check if the entry is a directory. If it is a directory, then you use getName method to get the name of the directory, and create a directory in File System by using mkdir method available in java.io.File. The PeopleCode segment is shown below:

   If (&EntryForZipFile.isDirectory()) Then
      Local JavaObject &dir = CreateJavaObject("java.io.File", &EntryForZipFile.getName());
      Local boolean &b = &dir.mkdir();


4. Extract Bytes from ZIP File


If the entry is a non directory (i.e. File) then you have to read the bytes from the entry and write the bytes read to a file on disk. Note, that when you use getNextEntry method, the stream is automatically positioned at the beginning of the entry data. So, to read bytes from the entry, all you have to do is to use read method, and read the bytes to a byte array in PeopleCode. You have to recursively do this, until all bytes from the Zip entry is read. You will learn about this, when you see the full PeopleCode example. For now, here is a split segment;

Local JavaObject &ByteBufferArray = CreateJavaArray("byte[]", 1024); /* Create Byte Array to read Zip entry bytes */
Local number &NumberOfBytes; /* Variable to loop through number of bytes */

.................
.................

&NumberOfBytes = &InputZipReader.read(&ByteBufferArray); /* Read First set into byte array */
      
      While &NumberOfBytes > 0 /* Loop until all bytes are read */
      /* Here, we will write code to write the bytes read to a file on disk */
         &NumberOfBytes = &InputZipReader.read(&ByteBufferArray); /* Read next set of bytes */
      /* We exit the loop when all bytes are read */   
      End-While;
/* We have to close the entry at the end, so that the stream becomes ready to read next entry */      
&InputZipReader.closeEntry();      

Note that at the end of the process, we are invoking a closeEntry method, that closes the current entry and moves the pointer to read the next entry.

5. Write ZIP Entry to Disk


Now, while you are reading bytes from every ZIP file as per Step 4, you can write the entry to disk, by creating a FileOutputStream object and pass the base directory / Entry path details to it. Then you can use the write method to write the bytes to the output stream, that you have read in step 4. The PeopleCode snippet is shown below:

      Local JavaObject &out = CreateJavaObject("java.io.FileOutputStream", "c:\temp\" | &EntryForZipFile.getName(), True);
      ......
      ......
      &out.write(&ByteBufferArray, 0, &NumberOfBytes);

6. Read / Extract All Other ZIP Entries


We repeat steps 3, 4 and 5 in a loop until all the entries in the ZIP files are read. The method getNextEntry returns NULL, when there are no more entries to be read in the input file. So, we make use this factor as a looping condition to read all the entries. Inside the loop, we handle directories and files as discussed earlier. ( refer code below )

7. Close Input ZIP File


At the end of step 6, we are done extracting the ZIP file completely. So, we can now close the input ZIP file by using the close method in ZipInputStream class. This step closes the input stream and releases memory back.


Complete PeopleCode – Read / Extract ZIP File


The complete PeopleCode snippet to read / extract ZIP file is shown below. Note that we have handled directories and files separately, as we found that in some cases, the directories are read after the file is read. You can tweak the code to suit to your needs and get rid of the directories code section completely, if you don’t want it.

/* Extract ZIP Files in PeopleCode - Example */
Local JavaObject &InputZipReader = CreateJavaObject("java.util.zip.ZipInputStream", CreateJavaObject("java.io.FileInputStream", "c:\temp\TESTER.zip"));
Local JavaObject &EntryForZipFile = &InputZipReader.getNextEntry();
Local JavaObject &ByteBufferArray = CreateJavaArray("byte[]", 1024);
Local number &NumberOfBytes;

/* Create all directories first */
While &EntryForZipFile <> Null
   If (&EntryForZipFile.isDirectory()) Then
      Local JavaObject &dir = CreateJavaObject("java.io.File", "c:\temp\" | &EntryForZipFile.getName());
      Local boolean &b = &dir.mkdir();
   End-If;
   &EntryForZipFile = &InputZipReader.getNextEntry();
End-While;
&InputZipReader.close();

/* You can combine the code if required. I have split the code as Java read files first and directories next in some cases */
/* Now we handle files */

&InputZipReader = CreateJavaObject("java.util.zip.ZipInputStream", CreateJavaObject("java.io.FileInputStream", "c:\temp\TESTER.zip"));
&EntryForZipFile = &InputZipReader.getNextEntry();

While &EntryForZipFile <> Null
   If (&EntryForZipFile.isDirectory()) Then
      /* do nothing */
   Else
      Local JavaObject &out = CreateJavaObject("java.io.FileOutputStream", "c:\temp\" | &EntryForZipFile.getName(), True);
      &NumberOfBytes = &InputZipReader.read(&ByteBufferArray);
      While &NumberOfBytes > 0
         &out.write(&ByteBufferArray, 0, &NumberOfBytes);
         &NumberOfBytes = &InputZipReader.read(&ByteBufferArray);
      End-While;
      /* You can read the file into PeopleSoft at this stage if required */
      &InputZipReader.closeEntry();
   End-If;
   &EntryForZipFile = &InputZipReader.getNextEntry();
End-While;


This code produces the same output as shown in the expected output section earlier. That completes our tutorial to extract ZIP files in PeopleCode. Feel free to discuss any questions that you have on this in the comments section of this blog.