Try this:
drop table t;
create table t
as
select owner, object_type, object_name
from all_objects
/
-- watching too much tv?
create or replace view tv (
last_name, first_name, address
)
as
select
substr(owner,1,10)
, substr(object_type,1,10)
, substr(object_name,1,10)
from t
/
select text
from user_views
where view_name = 'TV'
/
| "Poras, Henry R." <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 12/04/2003 08:09 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: View definition oddities
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." <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/03/2003 11:49 AM | To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
