Could not find alias names

[sangisiva]

Hi All,

I have a view in oracle 9i. This view contains totally 550 columns in it.

When I refer the view description , I am not seeing any alias names specified over there. But when I select * from the view , I get totally different names... (Should be alias names)

Is that possible to specify the alias names separately from View description? Where do I find the alias names for this view?

Any help would be appreciated. Thanks in Advance

 

[LKBrwnDBA]

DESC should do it:

SQL> create or replace view my_vw
  2  (enum, enm, j) as
  3* select empno, ename, job from emp;

View created.

SQL> desc my_vw
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENUM                                      NOT NULL NUMBER(4)
 ENM                                                VARCHAR2(10)
 J                                                  VARCHAR2(9)

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Sangisiva,

My apologies, but I don't clearly understand your problem. (I have never seen a "DESCRIBE <view name>" whose column aliases differed from the "select * from <view name>;"

Could you please post code/query/describe snippets that illustrate your problem?

Thanks

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[sangisiva]

Hi all,

Thanks for the reply..

Thats what I try to say.. I have never seen a view which does not have the alias names in its select statement but it differs when we select * from the view.

for eg, I take the above code ...

SQL> create or replace view my_vw 2 as
select empno, ename, job from emp;

View created.

SQL> desc my_vw
Name Null? Type
----------------------------------------- -------- ----------------------------
ENUM NOT NULL NUMBER(4)
ENM VARCHAR2(10)
J VARCHAR2(9)

SQL>

This is how I get.

 

[jimirvine]

Hmmm, are you 'tweaking' your script to simplify it for us before you post, or is that the EXACT script that you are running?

 

[SantaMufasa]

Sangisiva,

There is no way that your code, above, will work as posted:

create or replace view my_vw 2  as
   select empno, ename, job from emp;

create or replace view my_vw 2  as
                             *
ERROR at line 1:
ORA-00905: missing keyword

Since you are talking about a VIEW, there is no harm is simply re-running the code and please post the actual code-run results, along with the 'View Created' message.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[jimirvine]

Gaaaaa, you sprung my trap Dave ;D

 

[SantaMufasa]

Sorry, Jim, my foot is still bleeding all over the place.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[jimirvine]

LOL,

 

[sangisiva]

Friends,

I just changed the LKBrwnDBA 's script to explain my issue.. This is not the script I am running.

I can not post my original query here.. Thats y I have taken the example script.

The view which is I am working on is not created by me. It is the existing view in the database.I am analyzing this view for one of my project requirement.

So, when I view the definition of the view , there are no alias names specified even at the column level and not at the top. But when do DEscribe <view> gives me the different names.. so confused... Still searching where they could have created the alias nameds for the view...

I know we can create views as follows:

create or replace view test_v
(naame, numbe)
as
select ename,empno from emp

as well

create or replace view test_v as
select ename naame,empno numbe from emp

But the view I am working on does not have the alias names in its definition neither at the top nor at the column level... Tired searching for it :-(

 

[LKBrwnDBA]

You still have not provided any proof of what you say.

Oracle allways will DESCRIBE the view with column aliases same as the "select * from <view name>;"



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Sangisiva,

The other explanation of this behaviour is that you are inadvertently dealing with two different VIEWs. This could happen by[ul][li]Someone else owning a VIEW named "TEST_V",[/li][li]That owner of that view does a GRANT SELECT TO [PUBLIC | <role> | <your username>],[/li][li]Someone does a CREATE PUBLIC SYNONYM <synonym_name> ON <user.view_name>;[/li][/ul]This is a fairly typical scenario. It can be rather confusing to those who are unaware of the scenario.


You can find out if there are other owners of objects with the same name as the "TEST_V" view with this query:

col object_type format a20
col obj format a40
select object_type
      ,owner||'.'||object_name obj
  from all_objects
 where object_name = 'S_EMP';

OBJECT_TYPE        OBJ
------------------ -------------
SYNONYM            PUBLIC.S_EMP
SYNONYM            DHUNT.S_EMP
TABLE              SUMMIT.S_EMP
TABLE              TESTNEW.S_EMP
TABLE              TEST.S_EMP

Simply change the string S_EMP, above to contain the name of the object (e.g. name of your VIEW), for which you are trying to identify possible conflicts.

Please post your findings here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[sangisiva]

Hi SantaMufasa ,

sorry , I should have added it earlier in my post. Yes. The view is not in my schema. It is in another schema. I just have the SELECT permission on the view.

After reading your post, I checked in the schema in which the view is created, and awesome, the alias names are there at the top

create or replace view test_v
(naame, numbe)
as
select ename,empno from emp

in the view definition. Great !

But I am wondering how the permission can be granted partially... If you have a SELECT permission for an View Object, then you will be able to see the full definition on the view.????

 

[SantaMufasa]

If you have a SELECT permission for an View Object, then you will be able to see the full definition on the view.????

Correct.


The privileges of a VIEW are (in alphabetical order):[ul][li]DEBUG[/li][li]DELETE[/li][li]FLASHBACK[/li][li]INSERT[/li][li]ON COMMIT REFRESH[/li][li]QUERY REWRITE[/li][li]REFERENCES[/li][li]SELECT[/li][li]UPDATE[/li][/ul]If you have any privilege on a queriable object (e.g., TABLE, VIEW), then you can do a "DESCRIBE" on that object, thus seeing the definition of the object.


Let us know if this answers your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[sangisiva]

If you have any privilege on a queriable object (e.g., TABLE, VIEW), then you can do a "DESCRIBE" on that object, thus seeing the definition of the object.

But , how the alias names are not shown in the view definition in my schema!? But in the object owner's schema I can view the view definition with it alias names.. Strange !!!

 

[Turkbear]

Hi,
Are you sure that there is not also a view by that name in your schema? It is likely to be different altogether.


I am still not sure what you mean by 'showing' the alias names...The view definition is not what you see when you do a DESCRIBE, you see the view's structure...To see its definition you need to query the dictionary table ( user_views,all_views,dba_views - depending on access level) and read the TEXT field ....Or use one of Oracle's tools like Enterprise Manager to see its code..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

But , how the alias names are not shown in the view definition in my schema!? But in the object owner's schema I can view the view definition with it alias names..

Sangisiva, how many different VIEWs are you talking about here? We are flying blind.


Could you please post the CREATE VIEW code (including the "View created" confirmation), followed immediately with a DESCRIBE of the VIEW?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.