Parsing order (Was: "select ... connect by... " in the view)

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Parsing order (Was: "select ... connect by... " in the view)

Parsing order (Was: "select ... connect by... " in the view)

2004-03-10 - By Igor Neyman

Ok, I see no takers on my original question (how to create a view based
on "select . connect by. and prior. start with. " to get the hierarchy).

May be this will spark some interest, I guess it 's about parsing order.

The view I 'm using looks like this:

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Mar 10 08:42:51 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL > create or replace view test_view_ms as

2 SELECT

3 vt.model_id, vt.lvl "LEVEL ", vt.parent_subassembly_id,
vt.subassembly_id

4 FROM test_ms tm, TABLE(m_tree(tm.model_id)) vt;

View created.

And it works fine; "m_tree " is a function that hides "select . connect
by. and prior. start with. ".

"test_ms " table added to the join in order to be able to specify
"model_id " when querying this view.

But, if I change the order in the "FROM " list, I get an error:

SQL > create or replace view test_view_ms as

2 SELECT

3 vt.model_id, vt.lvl "LEVEL ", vt.parent_subassembly_id,
vt.subassembly_id

4 FROM TABLE(m_tree(tm.model_id)) vt, test_ms tm;

FROM TABLE(m_tree(tm.model_id)) vt, test_ms tm

*

ERROR at line 4:

ORA-00904 (See ORA-00904.ora-code.com): "TM ". "MODEL_ID ": invalid identifier

Is it because of the way (order) Oracle is parsing this view?

Igor Neyman, OCP DBA

ineyman@(protected)

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Igor Neyman
Sent: Thursday, February 26, 2004 11:44 AM
To: oracle-l@(protected)
Subject: "select ... connect by... " in the view

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

SELECT

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

ineyman@(protected)

<html >

<head >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=us-ascii " >

<meta name=Generator content= "Microsoft Word 10 (filtered) " >

<style >
<!--
/* Font Definitions */
@(protected)
 {font-family:Tahoma;
 panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
 {margin:0in;
 margin-bottom:.0001pt;
 font-size:12.0pt;
 font-family: "Times New Roman ";}
a:link, span.MsoHyperlink
 {color:blue;
 text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
 {color:purple;
 text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
 {margin:0in;
 margin-bottom:.0001pt;
 font-size:10.0pt;
 font-family: "Courier New ";}
span.emailstyle17
 {font-family:Arial;
 color:windowtext;}
span.EmailStyle19
 {font-family:Arial;
 color:navy;}
@(protected) Section1
 {size:8.5in 11.0in;
 margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
 {page:Section1;}
-- >
</style >

</head >

<body lang=EN-US link=blue vlink=purple >

<div class=Section1 >

 Ok, I see no takers on my original question
(how to create a view based on “select … connect by… and
prior… start with…” to get the hierarchy). 

 May be this will spark some interest, I
guess it’s about parsing order. 

 

 The view I’m using looks like this: 

 

 SQL*Plus: Release 9.2.0.1.0 - Production
on Wed Mar 10 08:42:51 2004 

 

 Copyright (c) 1982, 2002, Oracle
Corporation. All rights reserved. 

 

 Connected to: 

 Oracle9i Enterprise Edition Release
9.2.0.1.0 - Production 

 With the Partitioning, OLAP and Oracle
Data Mining options 

 JServer Release 9.2.0.1.0 - Production 

 

 SQL> create or replace view test_view_ms
as 

 2 SELECT 

 3 vt.model_id, vt.lvl
"LEVEL", vt.parent_subassembly_id, vt.subassembly_id 

 4 FROM test_ms tm, TABLE(m_tree(tm.model_id))
vt; 

 

 View created. 

 

 And it works fine; “m_tree” is
a function that hides “select … connect by… and prior…
start with…”. 

 “test_ms” table added to the
join in order to be able to specify “model_id” when querying this
view. 

 

 But, if I change the order in the “FROM”
list, I get an error: 

 

 SQL> create or replace view test_view_ms
as 

 2 SELECT 

 3 vt.model_id, vt.lvl
"LEVEL", vt.parent_subassembly_id, vt.subassembly_id 

 4 FROM TABLE(m_tree(tm.model_id))
vt, test_ms tm; 

 FROM TABLE(m_tree(tm.model_id)) vt, test_ms
tm 

 
* 

 ERROR at line 4: 

 ORA-00904 (See ORA-00904.ora-code.com):
"TM"."MODEL_ID": invalid identifier 

 

 Is it because of the way (order) Oracle is
parsing this view? 

 

<div >

 Igor Neyman, OCP DBA 

 ineyman@(protected) 

 

</div >

 

 -- --Original Message-- -- 
 From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On
Behalf Of Igor Neyman 
 Sent: Thursday,
February 26, 2004 11:44 AM 
 To: oracle-l@(protected) 
 Subject: "select ... connect
by..." in the view 

 

 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 

 SELECT 

 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 

 ineyman@(protected) 

 

 

</div >

</body >

</html >