| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ? strange Data Dictionary results: views and column alias
On Thu, 29 Apr 1999 15:27:46 GMT, "Babette Turner-Underwood"
<babettet_at_tmi.ca> wrote:
>
>Hello everyone.
>
>I am having a problem determining how to re-create a view from
>the data dictionary. I thought that I could use the text from user_views
>to find the way the view was defined. However, this does not
>seem to work for column alias on views. Any ideas?
>==============================================
>
>For instance:
>
>SQL> select text from user_views
> where view_name = 'FAX_SUBSCRIBERS';
>
>TEXT
>----------------------------------------------------------------------------
>----
>select
> sub_id, pin, status, sp_id, serial_number, fax_featcd,
> mt_signaling_code, sub_type
>from SUBSCRIBER_at_FAX_SERVER
>
>=================================================
>
>BUT, when I descrive the view, I get:
>
>SQL CMISPROD> desc fax_subscribers
> Name Null? Type
> ------------------------------- -------- ----
> TELEPHONE_NUMBER NOT NULL VARCHAR2(10)
> MT_IU_PIN VARCHAR2(6)
> MT_IU_STATE NOT NULL CHAR(1)
> DIST_ID VARCHAR2(4)
> MT_IU_ID NOT NULL VARCHAR2(30)
> MT_IU_FEATCD NOT NULL VARCHAR2(20)
> MT_SIGNALING_CODE VARCHAR2(2)
> SUBSCRIBER_TYPE NOT NULL CHAR(1)
>
>
>dba_tab_columns provides the column names above.
>So where is the column alias stored??
>I need to be able to extract the "original" definition of the view so
>I can re-create it on another instance. I do not want to use export / import
>to get the view as I wish to check the definition into source code control
>and export / import does not do this.
This is normal -- it stores only the exact string you typed in after the "AS" ...
An easy way around this is to put the aliases in the select statement, such as
create view xyz as
select sub_id telephone_number, pin mt_iu_pin
from subscriber;
Also, I have a script that does what you've mentioned - gets all the aliases from all_tab_columns and builds the view definition statement including the aliases, if you'd like it.
Chris
 |
 |