Column aliases not displaying in view source from different schema

[Mark Heckman]

When I look at the source for a view in a schema other than mine it does not show column aliases.

I create a view in one schema like this:
create or replace view test_vw (col1) as
select col2 from test_tb

Now I login as a different user and look at the source of the view and I see something like:
create or replace view other_schema.test_vw as
select col2 from test_tb

When I retrieve data from the view, the column is properly aliased.

I found old posts in this forum identifying the same problem and responses indicating that the problem has been resolved.

I looked at the Support Info dialog of PL/SQL Developer and it shows:
PL/SQL Developer Version 7.0.3.1123 (MBCS)
OCI: version 9.2
Oracle9i Enterprise Edition Release 9.2.0.4.0

The database I am testing on is version 9.2.0.4.0.

Thanks in advance for any assistance,
Mark Heckman

 

[Marco Kalter]

Does the user have access to the underlying objects of the view?

 

[Mark Heckman]

No, in my scenario the second user did not have access to the table referenced in the view.

I tried it again where the second user DID have access to the table referenced in the view. It still failed to display the aliased column names.

I tried it another time where the second user had access to the table referenced in the view but this time I also specified the schema for the table like this:
create or replace view test_vw (col1) as
select col2 from myschema.test_tb

This made the view source include the aliased column names.

Normally I don't specify my own schema name when creating views within my schema.

When PL/SQL Developer displays the source for a view, does it use some Oracle command to retrieve the source or does it retrieve data directly from sys.all_views and sys.all_tab_cols and assemble it together? I am curious because I built a code review utility once and had to query the sys.all_views table directly myself. I never gave aliased column names any thought.

The larger question is: Is their a solution that doesn't involve me recreating all views (with aliased column names) to include source schema names? This, of course, won't fix any of our purchased database applications.

Thanks for your attention to this matter. I think PL/SQL Developer is a great tool and makes my job easier.

 

[Marco Kalter]

The only way to obtain the column list is to parse the SQL, so this is where it fails. One possible workaround is to use inline aliases:

create or replace view test_vw as
select col2 as col1 from test_tb