Just wonder if anyone has (or came across) better solution for this.
I had a request from developer to put “select … connect by…” into view.
He wants to get the hierarchy in one step, but the “framework” he is using doesn’t allow him to issue “select … connect by…”.
I’ve got rather ugly solution for him (which I can share if anyone interested, didn’t want this message to be long), using user types, function, and the final view looks like this:
create or replace view test_view_ms as
vt.model_id, vt.lvl "LEVEL", vt.parent_subassembly_id, vt.subassembly_id
FROM test_ms tm, TABLE(m_tree(tm.model_id)) vt;
So, now he can get the hierarchy for specified model with just:
select from test_view_ms where model_id = …
Igor Neyman, OCP DBA