New feature in 11gR2 - listagg

Although this is more straight SQL than PL/SQL it is something I dicovered recently and found interesting. With Release 2 of the 11g Oracle database they have added, amoungst other things, a new built in function to created a concatenated list of values from a query. The new function is listagg. So you can create output of the form.


Dept Code Employees ID's
10  213, 214, 216, 218
20  220, 230, 298
30  450, 451, 850, 021, 823


Which you need to do occasionally. I have seen a requirement like this to create a comma separated list of email addresses to create an email from a table.
For example if you wanted code to create a mailing list from the employee ID's above based on department so that you could an email per department.


Dept  Employees email
10  max@company.com, bob@company.com, jen@company.com
20    cat@company.com, tom@company.com


The way this could be done was to loop through a cursor and while the value of department code was the same value as the one for the previous loop keep adding to the string of addresses. When the department value changed then complete the previous string and start the next string for the new department code. Which is easy to do with PL/SQL.

To do this with listagg it would look like this


SELECT emp.dept_id,
listagg(emp.email_addr, ', ') -- the column used in the
concatenated string and the delimiter
  within GROUP
  (ORDER BY emp.emp_id asc)  -- the order of values within the
concatenated string
FROM employee emp
WHERE emp.email_addr IS NOT NULL
GROUP BY emp.dept_id
ORDER BY emp.dept_id



If you omit the delimiter argument

listagg(emp.email_addr)

the default is a straight concatenation with no delimiter at all like this


mary@company.combob@company.comalice@company.commike@company.com

So if you want to have a space instead you need to say


listagg(emp.email_addr, ' ')

which would give you


mary@company.com bob@company.com alice@company.com mike@company.com

Of course there are limitations to how big this resultant string can be. In this case it is the VARCHAR2(4000) limit. If you exceed 4000 characters you will get the error


ORA-01489: result of string concatenation is too long

To get around this error you need to take a different approach. This isn't as convenient as using listagg and will result in a CLOB but it will work with longer data. Below is an example using xmlagg where it concatenates all employee last_name records for each department_id and orders within the concatenated records by the last_name.


SELECT emp.department_id,
rtrim(
         xmlserialize(content
           extract(
             xmlagg(xmlelement("big_string", 
                               emp.last_name||',') 
                               ORDER BY emp.last_name)
           , '//text()'
           )
         )
       , ','
       ) as list
FROM employees emp
GROUP BY emp.department_id

I've also got another post using xmlagg at multiple levels if that is needed. The same approach could be applied here if needed.

If the approach above does not do what you need you can also use an example that was shown on the Ask Tom site.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402

If you know another solution then leave a comment.

Using CONTINUE in loops, new feature 11g

In ancient history I did a lot of programming in C (Cbreeze to be exact) which had embedded SQL inside C code. As with any database processing you tend to do a lot of processing using loops. One of the most used features in C was the continue statement. Which basically means stop processing this iteration of the loop and go to the next record. Which is very useful.

So when I became an Oracle PL/SQL programmer back with release 7 of the database I was a bit upset that there was no comparable continue statement.

Well eventually Oracle caught up and in release 11g we can now use continue and it even goes one step further than what C could do.

Below is a basic example of looping logic you might use without continue. I guess what you might do in a pre 11g database.
Set a variable if a condition is met and use that so that you don't execute the rest of the logic in a loop statement.

DECLARE
  v_skip_rest_of_loop BOOLEAN;
BEGIN
  FOR i IN 1 .. 10 LOOP
  v_skip_rest_of_loop := FALSE;
  IF mod(i, 3) = 0 THEN
  v_skip_rest_of_loop := TRUE;
    END IF;
  IF NOT v_skip_rest_of_loop THEN
  -- lots of other processing here we don't 
      -- want to do for this loop when mod 3 = 0
  dbms_output.put_line('On line:'||to_char(i));
  END IF;
  END LOOP;
END;

But with CONTINUE available you can simplify the logic as below. No need to set a variable, just say start the next record

BEGIN
  FOR i IN 1 .. 10 LOOP
IF MOD(i, 3) = 0 THEN
CONTINUE; -- start the next iteration of the loop
END IF;

  -- lots of other processing here we don't 

    -- want to do for this loop when mod 3 = 0

dbms_output.put_line('On line:'||to_char(i));
END LOOP;
END;

But Oracle has made it even more concise in 11g. You can specify the condition logic as part of the continue definition using CONTINUE WHEN so it includes the logic from the IF statement above.

BEGIN
FOR i IN 1 .. 10 LOOP
CONTINUE WHEN MOD(i, 3) = 0;

  -- lots of other processing here we don't 

    -- want to do for this loop when mod 3 = 0

dbms_output.put_line('On line:'||to_char(i));
END LOOP;
END;

Converting weeks, months and years

Recently there was a need to convert start and end dates to differing units of weeks, months and years in PL/SQL.
Although Oracle provides many date manipulation tools there are limited built in functions to do this sort of conversion. So here is a look at some approaches to do this sort of work.

For many of these examples the requirement was to return value as whole numbers and round up to the next highest value. I.e. If something was 40 days that means it cross 2 months. This is done using CEIL. If you want to round to the nearest value use ROUND or to use the lower value use TRUNC.

Using a start and end date

For weeks you use:

SELECT CEIL((end_date - start_dt)/7) FROM dual

So if end_date - start_dt = 23, then 23 / 7 = 3.285.., CEIL(3.285..) = 4
23 days gives us 4 weeks which is what we want.

For months you use:


SELECT CEIL(months_between(end_date, start_dt)) FROM DUAL

So if end_date is 31/12/2011 and start_dt is 17/02/2011 then months_between gives 10.451.., CEIL(10.451..) = 11 months.
Looking at the start and end date in this example we can see that it covers 11 whole months so this makes sense.

For years it is the same sort of thing but we just need to divide by 12


SELECT CEIL(months_between(end_date, start_dt)/12) FROM DUAL

End date is 31/12/2014, start_dt is 17/02/2011, month_between = 46.451..., divide by 12 = 3.870.., CEIL(3,870..) = 4
Again based on the start and end date 4 years is what we expect.

When converting units without specific dates

But what if I want to convert straight from 32 weeks to a number of months?

We know that a week is 7 days so we can just use an end date of sysdate and start date of sysdate - (32 * 7).
SELECT CEIL(months_between(sysdate , sysdate - (32 * 7))) FROM DUAL


Convert months to years or years to months is simply CEIL(number_of_months/12) and CEIL(number_of_years * 12)

Convert months to days.
This is never going to be 100% accurate if it is a generic calculation. We know some months have 30 days, some 31 and of course February can have 28 or 29. So it really depends what specific months you want to convert. If we don't have specific months then you can get close to an accurate figure using.

SELECT SYSDATE - ADD_MONTHS(SYSDATE, -number_of_months) FROM dual


So for 7 months
SELECT SYSDATE - ADD_MONTHS(SYSDATE, -7)FROM dual
gives a result of 212 days

Convert years to days.
This also present problems because a leap year has an extra day. So again it depends which set of years you want to convert. For financial processing the number of days in a year is usually set as 365.25 so the extra day is distributed across the four years.

Another way to do it without using the 365.25 is use the add_month built_in as well.

SELECT SYSDATE - ADD_MONTHS(SYSDATE, (-12 * number_of_years)) FROM dual


which for 4 years = 1461 and 3 years = 1095 when run right now. If you want to make sure it always gets the same answer then work from a standard date instead of sysdate. E.g. set the date to to_date('01/01/2000') instead of sysdate.

There may be a more accurate way of doing these calculations but I have not come across it yet.