| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: View definition oddities
Content-Type: text/plain; charset="us-ascii"
Try this:
drop table t;
create table t
as
select owner, object_type, object_name
from all_objects
/
select text
from user_views
where view_name = 'TV'
/
"Poras, Henry R." <Henry_Poras_at_dfci.harvard.edu>
Sent by: ml-errors_at_fatcity.com
12/04/2003 08:09 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: View definition oddities
Jared,
Thanks, but what I was wondering is how the views got created in the first place. If the field name needs an alias (i.e. substr(x,i,j)) and you include this alias in the CREATE VIEW ddl, it will also appear in dba_views.text. If you don't include the alias in the DDL, the view won't be created. Yet we have views with no aliases in dba_views.text, yet these aliased (column names) are in column_name.dba_tab_columns. Also, if I just run the script from dba_views.text I get the error saying I need the alias. How did these views get into the database?
Henry
-----Original Message-----
Sent: Wednesday, December 03, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
The DBA_VIEWS view does not include the column names in the view.
If you are really diligent, you could probably tear apart a trace file and learn how oracle is reassembling the view from the DD. A hard parse on a view can produce 250k of trace file.
It is easier to recreate by generating the ddl from a combination of all_tab_columns and all_views.
Been there, done that. :)
Jared
"Poras, Henry R." <Henry_Poras_at_dfci.harvard.edu>
Sent by: ml-errors_at_fatcity.com
12/03/2003 11:49 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
cc:
Subject: View definition oddities
I found something strange when trying to copy a view from one database to
another. I spooled the text from dba_views and ran the CREATE VIEW AS ...
statement. There was an error because one of the fields needed an alias
(it was
SUBSTR(xxx)). I went back to the source database and poked around a bit.
The
view definition from dba_views had no field aliases, but dba_tab_columns
did.
For example, dba _views would show something like:
View_name: test
Text: SELECT SUBSTR(name1,3) FROM emp
While dba_tab_columns would have:
Table_name: emp
Column_id: 1
Column_name: ename
as if the view_text was SELECT SUBSTR(name1,3) ename FROM emp
What's up here?
Henry
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Poras, Henry R. INET: Henry_Poras_at_dfci.harvard.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --=_alternative 006376CE88256DF2_= Content-Type: text/html; charset="us-ascii"Received on Thu Dec 04 2003 - 12:09:26 CST
<br><font size=2 face="sans-serif">Try this:</font>
<br>
<br><font size=2 face="Courier">drop table t;</font>
<br>
<br><font size=2 face="Courier">create table t</font>
<br><font size=2 face="Courier">as</font>
<br><font size=2 face="Courier">select owner, object_type, object_name</font>
<br><font size=2 face="Courier">from all_objects</font>
<br><font size=2 face="Courier">/</font>
<br>
<br><font size=2 face="Courier">-- watching too much tv?</font>
<br><font size=2 face="Courier">create or replace view tv (</font>
<br><font size=2 face="Courier"> last_name, first_name, address</font>
<br><font size=2 face="Courier">)</font>
<br><font size=2 face="Courier">as</font>
<br><font size=2 face="Courier">select</font>
<br><font size=2 face="Courier"> substr(owner,1,10)</font>
<br><font size=2 face="Courier"> , substr(object_type,1,10)</font>
<br><font size=2 face="Courier"> , substr(object_name,1,10)</font>
<br><font size=2 face="Courier">from t</font>
<br><font size=2 face="Courier">/</font>
<br>
<br><font size=2 face="Courier">select text</font>
<br><font size=2 face="Courier">from user_views</font>
<br><font size=2 face="Courier">where view_name = 'TV'</font>
<br><font size=2 face="Courier">/</font>
<br>
<br><font size=2 face="sans-serif"><br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>"Poras, Henry R." <Henry_Poras_at_dfci.harvard.edu></b></font>
<br><font size=1 face="sans-serif">Sent by: ml-errors_at_fatcity.com</font>
<p><font size=1 face="sans-serif"> 12/04/2003 08:09 AM</font>
<br><font size=2 face="sans-serif"> </font><font size=1 face="sans-serif">Please respond to ORACLE-L</font>
<br>
<td><font size=1 face="Arial"> </font>
<br><font size=1 face="sans-serif"> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></font>
<br><font size=1 face="sans-serif"> cc: </font>
<br><font size=1 face="sans-serif"> Subject: RE: View definition oddities</font></table>
<br>
<br>
<br><font size=2 color=blue face="Arial">Jared,</font>
<br><font size=3 face="Times New Roman"> </font>
<br><font size=2 color=blue face="Arial">Thanks, but what I was wondering is how the views got created in the first place. If the field name needs an alias (i.e. substr(x,i,j)) and you include this alias in the CREATE VIEW ddl, it will also appear in dba_views.text. If you don't include the alias in the DDL, the view won't be created. Yet we have views with no aliases in dba_views.text, yet these aliased (column names) are in column_name.dba_tab_columns. Also, if I just run the script from dba_views.text I get the error saying I need the alias. How did these views get into the database? </font>
<br><font size=3 face="Times New Roman"> </font>
<br><font size=2 color=blue face="Arial">Henry</font>
<br><font size=3 face="Times New Roman"> </font>
<br><font size=2 face="Tahoma">-----Original Message-----<b><br>
From:</b> ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]<b>On Behalf Of </b>Jared.Still_at_radisys.com<b><br> Sent:</b> Wednesday, December 03, 2003 4:54 PM<b><br> To:</b> Multiple recipients of list ORACLE-L<b><br> Subject:</b> Re: View definition oddities<br>
</font>
<br><font size=2 face="sans-serif"><br>
The DBA_VIEWS view does not include the column names in the view.</font><font size=3 face="Times New Roman"> <br>
</font><font size=2 face="sans-serif"><br>
If you are really diligent, you could probably tear apart a trace file and</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br> learn how oracle is reassembling the view from the DD. A hard parse</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br> on a view can produce 250k of trace file.</font><font size=3 face="Times New Roman"> <br>
</font><font size=2 face="sans-serif"><br>
It is easier to recreate by generating the ddl from a combination of</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br> all_tab_columns and all_views.</font><font size=3 face="Times New Roman"> <br>
</font><font size=2 face="sans-serif"><br>
Been there, done that. :)</font><font size=3 face="Times New Roman"><br>
</font><font size=2 face="sans-serif"><br>
Jared</font><font size=3 face="Times New Roman"> <br>
<br>
<br>
</font>
<table width=100%>
<tr valign=top>
<td width=2%>
<td width=44%><font size=1 face="sans-serif"><b>"Poras, Henry R." <Henry_Poras_at_dfci.harvard.edu></b></font><font size=3 face="Times New Roman"> </font><font size=1 face="sans-serif"><br>
Sent by: ml-errors_at_fatcity.com</font><font size=3 face="Times New Roman"> </font>
<p><font size=1 face="sans-serif"> 12/03/2003 11:49 AM</font><font size=3 face="Times New Roman"> </font><font size=2 face="sans-serif"><br>
</font><font size=1 face="sans-serif">Please respond to ORACLE-L</font><font size=3 face="Times New Roman"> </font>
<td width=53%><font size=1 face="Arial"> </font><font size=1 face="sans-serif"><br>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></font><font size=3 face="Times New Roman"> </font><font size=1 face="sans-serif"><br> cc: </font><font size=3 face="Times New Roman"> </font><font size=1 face="sans-serif"><br> Subject: View definition oddities</font></table>
<br><font size=3 face="Times New Roman"><br>
<br>
</font><font size=2 face="Courier New"><br>
I found something strange when trying to copy a view from one database to<br> another. I spooled the text from dba_views and ran the CREATE VIEW AS ...<br> statement. There was an error because one of the fields needed an alias (it was<br> SUBSTR(xxx)). I went back to the source database and poked around a bit. The<br> view definition from dba_views had no field aliases, but dba_tab_columns did. <br>
<br>
For example, dba _views would show something like:<br> View_name: test<br> Text: SELECT SUBSTR(name1,3) FROM emp<br>
<br>
While dba_tab_columns would have:<br> Table_name: emp<br> Column_id: 1<br> Column_name: ename<br>
<br>
as if the view_text was SELECT SUBSTR(name1,3) ename FROM emp<br>
<br>
What's up here?<br>
<br>
Henry<br>
<br>
-- <br> Please see the official ORACLE-L FAQ: http://www.orafaq.net<br> -- <br> Author: Poras, Henry R.<br> INET: Henry_Poras_at_dfci.harvard.edu<br>
<br>
Fat City Network Services -- 858-538-5051 http://www.fatcity.com<br> San Diego, California -- Mailing list and web hosting services<br> ---------------------------------------------------------------------<br> To REMOVE yourself from this mailing list, send an E-Mail message<br> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<br> the message BODY, include a line containing: UNSUB ORACLE-L<br> (or the name of mailing list you want to be removed from). You may<br> also send the HELP command for other information (like subscribing).</font><font size=3 face="Times New Roman"><br>
<br>
</font>
<br>
<br>
--=_alternative 006376CE88256DF2_=-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |