Just ran into a disturbing "feature". When you edit a view, the column aliases are not included in the generated SQL. If you save that view, you have just lost all the aliases, (as I just learned from my testing team). Things break, people get all upset, and it just sort of ruins your day. Please fix this. Thanks.
View Column Aliases Lost
- Thread starter alanmoor
- Start date
Could it be that these views are invalid?
------------------
Marco Kalter
Allround Automations
------------------
Marco Kalter
Allround Automations
No, when you right click on a view and select "edit", it pops up a sql editor with a statement like:
create or replace view xyz as
select something from somwhere
instead of:
create or replace view xyz (someothername) as
select something from somewhere
When you save the view, the view will contain a column called "SOMETHING" instead of what it should be: "SOMEOTHERNAME".
Just to clarify, as my original post sounded kind of negative, I really like the product, it just needs a tweak here and there, just like most of the software I write
Alan Moor
create or replace view xyz as
select something from somwhere
instead of:
create or replace view xyz (someothername) as
select something from somewhere
When you save the view, the view will contain a column called "SOMETHING" instead of what it should be: "SOMEOTHERNAME".
Just to clarify, as my original post sounded kind of negative, I really like the product, it just needs a tweak here and there, just like most of the software I write
Alan Moor
Can you send me the view DDL?
------------------
Marco Kalter
Allround Automations
------------------
Marco Kalter
Allround Automations
What tripped me up was the aliasing of column MBR_EXPIRATION_DT to MBR_EXPIR_DT. Note also the "WITH READ ONLY" clause at the end. Thanks for taking a look at this.
CREATE OR REPLACE VIEW MZ_CANCEL_MBR_V ( MEMBERSHIP_KY,
MEMBER_KY, ASSOCIATE_ID, MBR_NAME, PAY_AMT,
DUES_AMT, MBR_EXPIR_DT, CLUB_CD, PAID_BY_CD,
CANCEL_DATE, STATUS, COST_EFFECTIVE_DT ) AS
--USAGE: For display on the Cancel Member page
SELECT x.membership_ky,
x.member_ky,
x.associate_id,
x.mbr_name,
x.pay_amt,
x.dues_amt,
x.member_expiration_dt,
x.club_cd,
y.paid_by_cd,
x.cancel_date,
x.status,
y.cost_effective_dt
from (select a.membership_ky,
a.member_ky,
b.associate_id,
b.last_name| |', '| |b.first_name mbr_name,
sum(nvl(a.PAYMENT_AT,0)) pay_amt,
sum(nvl(a.DUES_COST_AT,0)) dues_amt,
b.member_expiration_dt,
a.club_cd,
to_char(sysdate, 'mm/dd/yyyy') as cancel_date,
b.status
FROM mz_rider a, mz_member b
WHERE a.member_ky = b.member_ky
and (a.status in('A','P'))
and (b.status = 'A' or b.status = 'P')
group by a.membership_ky,
a.member_ky,
b.associate_id,
b.last_name| |', '| |b.first_name,
b.member_expiration_dt,
a.club_cd,
b.status
) x, mz_rider y
WHERE x.member_ky = y.member_ky
and y.rider_comp_cd = 'BS'
WITH READ ONLY
CREATE OR REPLACE VIEW MZ_CANCEL_MBR_V ( MEMBERSHIP_KY,
MEMBER_KY, ASSOCIATE_ID, MBR_NAME, PAY_AMT,
DUES_AMT, MBR_EXPIR_DT, CLUB_CD, PAID_BY_CD,
CANCEL_DATE, STATUS, COST_EFFECTIVE_DT ) AS
--USAGE: For display on the Cancel Member page
SELECT x.membership_ky,
x.member_ky,
x.associate_id,
x.mbr_name,
x.pay_amt,
x.dues_amt,
x.member_expiration_dt,
x.club_cd,
y.paid_by_cd,
x.cancel_date,
x.status,
y.cost_effective_dt
from (select a.membership_ky,
a.member_ky,
b.associate_id,
b.last_name| |', '| |b.first_name mbr_name,
sum(nvl(a.PAYMENT_AT,0)) pay_amt,
sum(nvl(a.DUES_COST_AT,0)) dues_amt,
b.member_expiration_dt,
a.club_cd,
to_char(sysdate, 'mm/dd/yyyy') as cancel_date,
b.status
FROM mz_rider a, mz_member b
WHERE a.member_ky = b.member_ky
and (a.status in('A','P'))
and (b.status = 'A' or b.status = 'P')
group by a.membership_ky,
a.member_ky,
b.associate_id,
b.last_name| |', '| |b.first_name,
b.member_expiration_dt,
a.club_cd,
b.status
) x, mz_rider y
WHERE x.member_ky = y.member_ky
and y.rider_comp_cd = 'BS'
WITH READ ONLY
It is the readonly option that causes the problem. We'll fix this for 5.1.3, which will be available shortly.
------------------
Marco Kalter
Allround Automations
------------------
Marco Kalter
Allround Automations
Colin Main
Member
Hi all,
I had this problem with 5.1.2 so upgraded to 5.1.4 . Unfortunately I still get it with the following view definition:
I personally prefer the "select x AS y..." format but I am working with existing code here, and unfortunateyl I am breaking it when I make a minor change to a view!
I had this problem with 5.1.2 so upgraded to 5.1.4 . Unfortunately I still get it with the following view definition:
Code:
create or replace view dummy_view (USER_OR_GROUP, USER_ID, USER_CODE, PORTAL_USER, EXTERNAL_ID, EXTERNAL_CODE) as
select 'U', id, user_name, portal_user, grantee_id, grantee_code from portal.wwsec_person$, external_grantees
where upper(external_grantees.GRANTEE_CODE(+)) = user_name
UNION
select 'G', id, name, 'N', grantee_id, grantee_code from portal.wwsec_group$, external_grantees
where upper(external_grantees.GRANTEE_CODE(+)) = name AND external_grantees.user_or_group = 'G' WITH READ ONLYWhether your preference is "select x as y" or "create view 
as select x", the point remains that the tool should support all syntactically correct variations.
Stepping off my soap box now,
Cheers,
Alan
P.S. Have been using the product for several months now and really do like it.
as select x", the point remains that the tool should support all syntactically correct variations.Stepping off my soap box now,
Cheers,
Alan
P.S. Have been using the product for several months now and really do like it.
Can you send me the view definition?
Tom Hillson
Member
Something that happened to me...
If you attempt to edit a view from a user that is not the owner of the view, the column aliases do not show up.
I would typically edit the view make the changes and then connect to the owner of the view and then compile it. That causes the aliases to disappear.
Tom
If you attempt to edit a view from a user that is not the owner of the view, the column aliases do not show up.
I would typically edit the view make the changes and then connect to the owner of the view and then compile it. That causes the aliases to disappear.
Tom
Ralph Slate
Member
Does anyone know how, in Oracle, those aliases are stored, and how PL/SQL developer knows when to use them?
I have two views; one with column aliases before the SELECT clause, and one with them within the SELECT clause (my preferred syntax).
Anything in the SELECT clause is stored in the TEXT field of DBA_VIEWS. Anything above it is stored in some other metadata.
It appears as though aliases are stored in DBA_TAB_COLUMNS. How does PL/SQL Developer, or any other application, know to get the column names from there only under certain circumstances?
And while we're on the subject, how do you know if the view should be a CREATE VIEW vs. a CREATE OR REPLACE VIEW? This information is not in the DBA_VIEWS system view.
Thanks,
Ralph Slate
I have two views; one with column aliases before the SELECT clause, and one with them within the SELECT clause (my preferred syntax).
Anything in the SELECT clause is stored in the TEXT field of DBA_VIEWS. Anything above it is stored in some other metadata.
It appears as though aliases are stored in DBA_TAB_COLUMNS. How does PL/SQL Developer, or any other application, know to get the column names from there only under certain circumstances?
And while we're on the subject, how do you know if the view should be a CREATE VIEW vs. a CREATE OR REPLACE VIEW? This information is not in the DBA_VIEWS system view.
Thanks,
Ralph Slate
Whether to create or replace? If it exists, replace it, if not, create it. Generally speaking, the advantage of CREATE vs CREATE OR REPLACE is that the CREATE will fail if the view already exists.
When to get column names from dba_tab_columns (or user_tab_columns) for views? ALWAYS. The essential problem is that PSD is trying to create a single generic command for view creation, when the proper approach would be to provide a custom dialog window, just like it does for other database objects.
When to get column names from dba_tab_columns (or user_tab_columns) for views? ALWAYS. The essential problem is that PSD is trying to create a single generic command for view creation, when the proper approach would be to provide a custom dialog window, just like it does for other database objects.
Ralph Slate
Member
This isn't correct. Try this yourself; create a view using CREATE VIEW... syntax. Then view the source in PLSD. You'll see the view start out CREATE VIEW ...
Now create a view using CREATE OR REPLACE VIEW... syntax, and then view the source in PLSD. You'll see it says CREATE OR REPLACE VIEW...
Somehow, PLSD knows to show the exact syntax of how the view was created. But that info isn't in the TEXT column of USER_VIEWS, nor is it in any other column of that system view.
Now with the aliases, the behavior is the same way. If you create a view using the column aliases before the SELECT statement, PLSD will show you the source the exact way you created it. If you create it without that list, it will not show the list when you view the source.
Somehow, PLSD knows to use either syntax.
My question is, how does it know?
Ralph
Now create a view using CREATE OR REPLACE VIEW... syntax, and then view the source in PLSD. You'll see it says CREATE OR REPLACE VIEW...
Somehow, PLSD knows to show the exact syntax of how the view was created. But that info isn't in the TEXT column of USER_VIEWS, nor is it in any other column of that system view.
Now with the aliases, the behavior is the same way. If you create a view using the column aliases before the SELECT statement, PLSD will show you the source the exact way you created it. If you create it without that list, it will not show the list when you view the source.
Somehow, PLSD knows to use either syntax.
My question is, how does it know?
Ralph
Ralph Slate
Member
That isn't correct either -- that view only contains code-based source, not views.
Ralph
Ralph
This is fixed for the next release.
It is planned for november.
Marko
This problem persists in Version 7.1.0.1337 almost 4 years after alanmoor's original posting.
If you expand the object using the explore you see to aliased column names, but when you extract (right click/edit) the view the aliased column names are not present, however if you select DBMS_Metadate/DDL they are, but so other DDL items that are not wanted.
This error presents a problem when exporting objects since if dependencies are built on the views aliased column name they will not compile.
Can this please be worked on.
Thanks
This problem persists in Version 7.1.0.1337 almost 4 years after alanmoor's original posting.
If you expand the object using the explore you see to aliased column names, but when you extract (right click/edit) the view the aliased column names are not present, however if you select DBMS_Metadate/DDL they are, but so other DDL items that are not wanted.
This error presents a problem when exporting objects since if dependencies are built on the views aliased column name they will not compile.
Can this please be worked on.
Thanks
Hmm, I think the conclusion was that there is no formal method to obtain the alias list. It is not stored in the dictionary.
D.
Member²
HI Marco,
The alias is effectively the column_name in DBA_TAB_COLUMNS. Agreed it's not exactly defined as an "alias", it's just the column has a different name from the SQL statement that created it.
I try to ensure all my developers use in-line aliases "eg. SELECT a AS b " instead of positional aliases in the CREATE statement, so that the alias is kept beside the column and is stored as part of the SQL statement. However, legacy code was not defined that way, probably because of code generation from tools like TOAD or ErWin.
The "DBMS_METADATA" menu option builds it correctly, so maybe the "View" menu option should call to that behind the scenes - just a suggestion.
Regards,
D.
The alias is effectively the column_name in DBA_TAB_COLUMNS. Agreed it's not exactly defined as an "alias", it's just the column has a different name from the SQL statement that created it.
I try to ensure all my developers use in-line aliases "eg. SELECT a AS b " instead of positional aliases in the CREATE statement, so that the alias is kept beside the column and is stored as part of the SQL statement. However, legacy code was not defined that way, probably because of code generation from tools like TOAD or ErWin.
The "DBMS_METADATA" menu option builds it correctly, so maybe the "View" menu option should call to that behind the scenes - just a suggestion.
Regards,
D.
These are the column names, which will indeed correspond to the aliases. This does however not tell you that aliases have been used.
That would work, but then people would complain that the view definition contains an alias list, even if they did not specify any (or specified them inline) when the view was created.
Maybe we should create a preference.
Sure 
In that case there is no problem with the current implementation.
D.
Member²
No, there is a problem. If you create an alias as positional (not inline), it is lost when you generate the SQL.
The preference is a perfect way to solve this. Those who always use inline aliases (and can be 100% sure of every view the DB having done that) can switch off the preference. The rest of us in the real world
can have the aliases generated, and give the us the ability to change them to inline at next release.
Regards,
D.
The preference is a perfect way to solve this. Those who always use inline aliases (and can be 100% sure of every view the DB having done that) can switch off the preference. The rest of us in the real world
can have the aliases generated, and give the us the ability to change them to inline at next release.Regards,
D.
I know. I was merely referring to the case mentioned by Gustavo.
