0

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?

2 Answers 2

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
0

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.

1
  • 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) Dec 7, 2021 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.