Oracle 12c PL/SQL improvements.

Last week I was giving a presentation for the UNYOUG (Upstate NY Oracle users Group) and I talked about the new features in 12c (along with In-Memory database).

I thought I would share some thoughts after the meeting.

I went through Tom Kyte's top 12 new features, and surprisingly the top feature that excited people was the PL/SQL improvements. 

The PL/SQL improvements had to do with the ability to write a PL/SQL as part of the query.

Lets say currently with 11g you have a function that includes the days between 2 dates.

  CREATE OR REPLACE FUNCTION Days_Between 
             (first_dt DATE, second_dt DATE)
                RETURN NUMBER IS 
       dt_one NUMBER; 
       dt_two NUMBER; 
BEGIN 
      dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD')); 
      dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));  
           RETURN (dt_two - dt_one); 
 END Days_Between;   

select Days_between(start_date,end_date) from mytable;



The problem is that in order to test this function you need to create the function.  There are multiple issues that developers face with having to do this.

1. Developers often don't have the authority to create/change functions, especially if they need to be owned by a different schema
2. Replacing the current function affects other users and this may not be desirable while debugging changes.
3. Testing against production data is often not possible because of authorization, and collision issues.

The answer in 12c is the ability to include a function in the "WITH" clause.. The above would become


WITH  FUNCTION Days_Between
             (first_dt DATE, second_dt DATE)
                RETURN NUMBER IS
       dt_one NUMBER;
       dt_two NUMBER;
BEGIN
      dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD'));
      dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));
           RETURN (dt_two - dt_one);
 END Days_Between;  
select Days_between(start_date,end_date) from mytable;

So. what about Procedures you ask ? You can also include procedures in the mix.  The main purpose of doing this is to include any procedures that are invoked from the function.  This way you can include all the "dependencies" in the with clause. 

Finally, I read an article talking about how much this improves performance too.

http://www.oracle-base.com/articles/12c/with-clause-enhancements-12cr1.php#plsql-support

but to the developers I talked to the big advantage was with the ability to test..

As far as performance gains, I don't know how much I would put reusable code (like functions) directly into a sql statement. It would be a bear to support any changes to a "common function" defined multiple places.