silentsurfer silentsurfer - 3 months ago 16
SQL Question

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

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

You have to remove the comma before the second function to make it work. I tried 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?