Create view with inline function in db<>fiddle

Question

I'm trying to create a view with in inline function in db<>fiddle.

I've removed the CREATE VIEW part for now, and am just trying to run the query, just to get to the root issue.

select /*+ WITH_PLSQL */ calc from
(
with
function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual
);
/

Source: Inline function in a select sub query or create view

When I run that code in db<>fiddle, I get an error:

ORA-00905: missing keyword

What am I doing wrong?

Answer 1

What went wrong

1. That feature didn't exist in 11.2
2. You used the question SQL, not the answer SQL

Correction

My answer is the same. Define it in the outer most WITH statement.

with
function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual

Answer 2

Building on @Michael Kutz' answer:

The following seems to work in the Oracle 18c version of db<>fiddle.

(I think 12c and beyond supports inline functions):

create or replace view v1 as (
select /*+ WITH_PLSQL */ calc from
(
with
function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual
)
)

db<>fiddle

---

Edit:

Unfortunately, any queries that reference that view need to have the select /*+ WITH_PLSQL */ hint in them:

select /*+ WITH_PLSQL */ 
 *
from
 v1

That's a deal breaker for me. The system that would be using the view is a COTS system. It's not possible for me to add the hint to the queries that would be referencing the view.

• Michael Kutz: In the link to the referenced source, the answer suggested that the requested statement can be used with an INSERT statements. (Which is what CTAS is)