Oracle 'WITH' and inline PL/SQL Functions in SQL queries

Answered

I try to use inline PL/SQL functions in an SQL query. This is how it looks and works:

with function sayHello(name in varchar2) return varchar2 is
begin
return concat('Hello ', name);
end;
select sayHello(U.name)
from Users U;

this nicely yields what one would expect: hello's for all users.

But when I add a inline query (as per documentation of Oracle 12c should be possible according to askTom: https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-12c-sql-query-with-inline-plsql-function#9538727800346688703) like so:

with function sayHello(name in varchar2) return varchar2 is
begin
return concat('Hello ', name);
end;
niceUsers as (select * from Users where Users.score > 7)
select sayHello(U.name)
from niceUsers U;

the result is that I get an error from the Syntax checker of DataGrip: "',' or <query expression> expected, got 'niceUsers'".

This makes it rather impossible to run the query as DataGrip does not know where to start or end the text to send to Oracle.

When running this same query from DBeaver (https://dbeaver.io) (it also has a problem with finding the start of the query but I can force it to send all text) I get the result I longed for and all is well. But I would like to stay within DataGrip.

Hope you can explain what I do wrong.

Note: I use DataGrip from within PHP Storm. I'm using version 2019.2.1 (#PS 192.6262.66) with a subscription until Jan 7, 2020.

 

Please sign in to leave a comment.