Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hello Ladies and Gents,

I've been trying to deploy a recursive query as a view in Oracle XE and Standard Edition, neither to much success.

The query is located in this question here: http://stackoverflow.com/questions/17358109/how-to-retrieve-all-recursive-children-of-parent-row-in-oracle-sql

with recursion_view(base, parent_id, child_id, qty) as (
   -- first step, get rows to start with
   select 
     parent_id base, 
     parent_id, 
     child_id, 
     qty
  from 
    md_boms

  union all

  -- subsequent steps
  select
    -- retain base value from previous level
    previous_level.base,
    -- get information from current level
    current_level.parent_id,
    current_level.child_id,
    -- accumulate sum 
    (previous_level.qty + current_level.qty) as qty 
  from
    recursion_view previous_level,
    md_boms        current_level
  where
    current_level.parent_id = previous_level.child_id
)
select 
  base, parent_id, child_id, qty
from 
  recursion_view
order by 
  base, parent_id, child_id

The query itself works and returns results. However, when I try to create a view with that query, I receive errors.

I've posted two screenshots: http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/ViewError.jpg and http://www.williverstravels.com/JDev/Forums/StackOverflow/17358109/InternalError.jpg The first one with the ! and * is when I am using JDeveloper 11g, using the Database Navigator, right-click on View and select "New View". I receive the error when I click OK. I can indeed create the view through a sql script, but when I attempt to view the data, I receive the ORA_00600 error.

I've tried this on both my local machine for XE (version 11.2.0.2.0) and on 11g Standard Edition (11.2.0.2.v6) via Amazon Web Services. The result is the same.

Does anyone know how to get around this?

share|improve this question
    
Not 5 minutes after I post this, I decide not to use JDev's graphical editor, and simply write SELECT * FROM BOMS_VIEW; And it works like a charm. I just can't use data tab to view the records in the editor. Wish I had known that 5 hours ago. –  Will Lovett Jun 28 '13 at 21:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.