Getting ORA-32042 even though i can create the view in SQL Developer

materialized-vieworacleoracle-12c

I have a strange problem creating a materialized view using the code generated by the export wizard.

I have created a materialized view and pasted following code into the sql query tab from the popup. Everything works fine and the view is there. But when i try to run this code in a simple sql query editor and press run script i get the following error message.

Database: 12c Release 1
Error code: ORA-32042
Description: recursive WITH clause must reference itself directly in one of the UNION ALL branches

What have i done wrong?

CREATE MATERIALIZED VIEW axchannel_customer_category_hierarchy_discount_mview (
    "CHANNEL_ID",
    "CATEGORY_ID",
    "CATEGORY_PARENT_ID",
    "CATEGORY_REC_ID",
    "CATEGORY_NAME",
    "CUSTOMER_ID",
    "DISCOUNT_PERCENT",
    "DISCOUNT_ORIG",
    "LVL"
) AS
    WITH prejoined_discounts AS (
        SELECT
            axchannel_category_hierarchy.*,
            axchannel_customer.id   AS customer_id,
            axchannel_customer.customer_nr,
            axchannel_customer_category_discount.discount_percent
        FROM
            axchannel_category_hierarchy
            CROSS JOIN axchannel_customer left
            JOIN axchannel_customer_category_discount ON axchannel_category_hierarchy.channel_id = axchannel_customer_category_discount
            .channel_id
                                                         AND axchannel_category_hierarchy.id = axchannel_customer_category_discount
                                                         .category_id
                                                         AND axchannel_customer_category_discount.customer_id = axchannel_customer
                                                         .id
        WHERE
            axchannel_category_hierarchy.channel_id = axchannel_customer.channel_id
    ),foo (
        channel_id,
        id,
        parent_id,
        category_rec_id,
        category_name,
        customer_id,
        discount_percent,
        discount_orig,
        lvl
    ) AS (
        SELECT
            c.channel_id,
            c.id,
            c.parent_id,
            c.category_rec_id,
            c.category_name,
            c.customer_id,
            c.discount_percent,
            c.discount_percent   AS discount_orig,
            1 AS lvl
        FROM
            prejoined_discounts c
        WHERE
            c.parent_id IS NULL
        UNION ALL
        SELECT
            c.channel_id,
            c.id,
            c.parent_id,
            c.category_rec_id,
            c.category_name,
            c.customer_id,
            CASE
                WHEN c.discount_percent IS NULL THEN foo.discount_percent
                ELSE c.discount_percent
            END discount,
            c.discount_percent   AS discount_orig,
            lvl + 1
        FROM
            foo
            JOIN prejoined_discounts c ON c.channel_id = foo.channel_id
                                          AND c.parent_id = foo.id
                                          AND c.customer_id = foo.customer_id
    )
    SELECT
          channel_id,
    id as category_id,
    parent_id as category_parent_id,
    category_rec_id,
    category_name,
    customer_id,
    discount_percent,
    discount_orig,
    lvl
    FROM
        foo;

Best Answer

Interesting. It works without the column alias in the mview definition.

with
  bar as (select * from dual),
  foo(lvl) as
  ( 
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

       LVL
----------
         1
         2
         3
         4
         5

Create mview fails with column alias in definition:

create materialized view mv1(lvl) as
with
  bar as (select * from dual),
  foo(lvl) as
  ( 
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Error report -
ORA-12018: following error encountered during code generation for "BP"."MV1"
ORA-32042: recursive WITH clause must reference itself directly in one of the UNION ALL branches
12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""
*Cause:    The refresh operations for the indicated materialized view could
           not be regenerated due to errors.
*Action:   Correct the problem indicated in the following error messages and
           repeat the operation.

But it works without the column alias:

create materialized view mv1 as
with
  bar as (select * from dual),
  foo(lvl) as
  ( 
    select 1 as lvl from bar
    union all
    select foo.lvl + 1 from foo join bar on (foo.lvl < 5)
  )
select lvl from foo;

Materialized view MV1 created.

I see no reason for your version not working, it may be a bug, but I could not find anything related to this on the support site of Oracle.

Above output is from version 18.4.

Related Question