Oracle 12c: Multiple functions in a SELECT statement's WITH clause

Question

select banner
from v$version
;


BANNER
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Solaris: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

With its 12c release, Oracle has added the functionality to allow the declaration of Pl/SQL functions directly at the top of an SQL statement (see https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1)

This can be quite a handy feature, esp. on projects where you need to pull data from DBs with user rights limited to SELECT statements.

A simple example:

with 
  function add_string(p_string in varchar2) return varchar2
  is
    --Function to add a string
    l_buffer varchar2(32767);
  begin
    l_buffer := p_string || ' works!';
    --
    return l_buffer;
    --
  end ; 
--
select add_string('Yes, it') as outVal
from dual
;

---------
OUTVAL
Yes, it works!

However, I have not yet been able to include multiple functions in the WITH clause:

with 
  function add_string(p_string in varchar2) return varchar2
  is
    --Function to add a string
    l_buffer varchar2(32767);
  begin
    l_buffer := p_string || ' works!';
    --
    return l_buffer;
    --
  end ; 
  --      
, function doesnt_it(p_string in varchar2) return varchar2
  is 
    l_buffer varchar2(32767);
  begin
    l_buffer := p_string || ' Doesnt it?';
    --
    return l_buffer;
    --
  end ; 
--
select add_string('Yes, it') as outVal
from dual
;

Throws ORA-00928: missing SELECT keyword. Does anybody know whether multiple function declarations are allowed with this new feature and if so, how they can be achieved?

Answer 1

You have to remove the comma before the second function to make it work. I tested with TOAD, sqlDeveloper and sqlPlus.

If you write the statement like this, combining both functions:

with 
  function add_string(p_string in varchar2) return varchar2
  is
    --Function to add a string
    l_buffer varchar2(32767);
  begin
    l_buffer := p_string || ' works!';
    --
    return l_buffer;
    --
  end ; 
  --      
  function doesnt_it(p_string in varchar2) return varchar2
  is 
    l_buffer varchar2(32767);
  begin
    l_buffer := p_string || ' Doesnt it?';
    --
    return l_buffer;
    --
  end ; 
--
select doesnt_it(add_string('Yes, it')) as outVal
from dual
;

you'll obtain:

OUTVAL
--------
Yes, it works! Doesnt it?