View based on SELECT with 'WITH' clause

Question

I've select with 'WITH' clause:

with 
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
select col1,col2 from alias3

I tryied to create view using:

create view ex_view as (
with 
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
select col1,col2 
from alias3
)

When I tried to execute this create statement got 'unsupported use of WITH clause'

How to create view based on my select statement properely?

whats the entire error? Usually there is an Oracle error number with that — Nick.Mc, Jan 12 '15 at 12:15
Does `col1` or `col2` also exist in `alias1` or `alias2`? Try `select alias3.col1, alias3.col2 from alias3` and make all other columns qualified with table names (or alias) — Wernfried Domscheit, Jan 12 '15 at 14:16

score 13 · Accepted Answer · answered Jan 12 '15 at 12:18

13

Try dropping the parentheses:

create view ex_view as
with 
    alias1 as (select...),
    alias2 as (select ... from alias1),
    alias3 as (select col1, col2 ... from alias2)
from alias3;

answered Jan 12 '15 at 12:18

Gordon Linoff

1,242,037
58
646
786

This solution doesn't work properely. I started to exexcute this code, passed 15 minutes but server hasn't created view. Instead of this, my client crashed. And crashes every time I try to create view in suggested by You way. Something is wrong. – R. Nec Jan 12 '15 at 13:49
@R.Nec . . . Something is wrong, but I'd be quite surprised if it were the view definition. Can you create *any* view? Can you run the query separately? – Gordon Linoff Jan 12 '15 at 19:41
@GordonLinoff think you are missing the `select col1, col2` statement before _from alias3_? :) – Kevad Jun 09 '20 at 16:43
This solved my problem, I was using `create table` instead of `create view`. – Dudelstein Oct 20 '20 at 11:22

score 4 · Answer 2 · answered Jan 12 '15 at 12:18

4

You shouldn't have the parentheses around the query; change it to:

create view ex_view as (
with 
alias1 as (select...),
alias2 as (select ... from alias1),
alias3 as (select col1, col2 ... from alias2)
select col1,col2 
from alias3
)

For example:

create view ex_view as
with 
alias1 as (select dummy from dual),
alias2 as (select dummy as col1, dummy as col2 from alias1),
alias3 as (select col1, col2 from alias2)
select col1,col2 
from alias3;

View ex_view created.

The same construct with the parentheses around the query gets ORA-32034: unsupported use of WITH clause too.

If you aren't actually using any of the subqueries in more than one level of query - so the 'common' part of 'common table expression' isn't really relevant - you could use inline views instead:

create view ex_view as
select alias3.col1, alias3.col2
from (
  select col1, col2
  from (
    select dummy as col1, dummy as col2
    from (
      select dummy from dual
    ) alias1
  ) alias2
) alias3;

But the with clause is valid, and often easier to read and maintain anyway.

answered Jan 12 '15 at 12:18

Alex Poole

183,384
11
179
318

Using `WITH` clause may improve performance, because under some circumstance while select Oracle creates a temporary table an selects from there. – Wernfried Domscheit Jan 12 '15 at 13:08
@Wernfried - it can materialise an inline view too, can't it? Another useful reason to use CTEs though, if not. I'm not saying not to use `with` anyway, just providing options. – Alex Poole Jan 12 '15 at 13:10
I don't know if Oracle can materialise an inline view. A quick google search did not give me a proper answer about that. – Wernfried Domscheit Jan 12 '15 at 13:19
@Wernfried Creating a global temporary table of an intermediate row source is a bad idea if that row source is only used once. – Jon Heller Jan 12 '15 at 13:55
@AlexPoole - I changed code using example You've posted, but execution fails, lasts forever and finally my client crashes. Everytime I try to create this view. – R. Nec Jan 12 '15 at 14:05
@r.Nec - creating a view is DDL and shouldn't take long. Which client, and what happens if you create it through SQL*Plus? Does the query run standalone? – Alex Poole Jan 12 '15 at 14:07
Query runs standalone properely. When changed in way You recommended, client crashes when i try to stop executing. PL/SQL developer, and SQL developer too. – R. Nec Jan 12 '15 at 14:10
@JonHeller, the Oracle optimizer creates the temporary table automatically, that's not a decission done by me. Maybe expression **temporary table** confuses some people, however using **materialise** may also confuse people with `MATERIALIZED VIEW` which is again different. – Wernfried Domscheit Jan 12 '15 at 14:14
@R.Nec - without your schema and actual view it's impossible to guess why. If the session is crashing then check the alert log on the server to see if there was an ORA-600 code, and if necessary raise a service request. [The principle here works](http://sqlfiddle.com/#!4/ba8fe3/2), so you have a separate issue. – Alex Poole Jan 12 '15 at 14:34
@AlexPoole - current view does not exist. I've select and I want to create view based on it. – R. Nec Jan 12 '15 at 14:55
@R.Nec - I understand that. But your actual view will be more complicated than my demo, and it is apparently doing something that is tripping Oracle up. There is not enough information to guess what is happening. Try to find any underlying error being thrown in the alert log. Then if you can't figure it out, ask a new question about that problem, search on My Oracle Support, or raise a service request. It seems to be beyond the scope of this question. – Alex Poole Jan 12 '15 at 15:00
@Wernfried Yes the optimizer may choose to create a temporary table. But as far as I know it will never do that if the common table expression is only used once. When I look for any instances of a SYS temporary table they all include statements where the CTE is used multiple times: `select * from gv$sql where sql_id in (select distinct sql_id from v$sql_plan_statistics_all where object_name like '%SYS_TEMP%');` – Jon Heller Jan 12 '15 at 16:57
@WernfriedDomscheit, the inline view is actually a [Derived Table](https://docs.oracle.com/cd/E57185_01/IRWUG/ch03s07.html) – Spon4ik Mar 05 '19 at 06:28

Philippe Malera · Answer 3 · 2020-05-01T08:25:21.223

0

Is there a way to create a view with a "WITH FUNCTION" clause.

CREATE OR REPLACE VIEW test$v AS 
    WITH
      FUNCTION with_function(p_id IN VARCHAR2) RETURN VARCHAR2 IS
      BEGIN
        RETURN p_id;
      END;
    SELECT with_function(dummy) AS dummy
    FROM   dual;
    /

This generate an error :

ORA-06553: PLS-103: Encountered the symbol "end-of-file"

edited May 01 '20 at 08:25

answered May 01 '20 at 08:05

Philippe Malera

21
3

Sorry, my bad, it was just an editor problem (not supported by PLSQL Developer), it run fine under SQL*Plus. – Philippe Malera May 01 '20 at 08:21
Found the answer, last semicolon must be removed before the slash. – Philippe Malera May 01 '20 at 09:39

View based on SELECT with 'WITH' clause

3 Answers3