Re: SQL Developer - view DDL Formatting

Hi, from page 701, document INCITS/ISO/IEC 9075-2:2011 [2012]

<view definition> ::= CREATE [ RECURSIVE ] VIEW <table name> <view specification>

      AS <query expression> [ WITH [ <levels clause> ] CHECK OPTION ]

<view specification> ::= <regular view specification> | <referenceable view specification>

<regular view specification> ::= [ <left paren> <view column list> <right paren> ]

As above it appears (the document really is an awful read) that the alias list is ANSI standard. If the purpose of retrieving the DDL is to migrate the VIEW to another DBMS that doesn't similarly implement aliasing this way, then fair cop. In a round about way Fergal I am asking what is the underlying reason for you wanting to strip out the alias list as listed in <regular view specification>?

~

Mike
http://www.strychnine.co.uk

> On 26 Apr 2017, at 12:35, Tefft, Michael J <Michael.J.Tefft_at_snapon.com> wrote:
>
> Ø I just dont see any good reason to use the attribute list.
>
> As a personal style or standard, you might choose not to override column names in the attribute list – but if you are extracting DDL for an existing view then you are at the mercy of whoever wrote that view.
>
> i.e. if the original author wrote
> CREATE OR REPLACE VIEW V (DOG) as SELECT DUMMY AS CAT FROM DUAL;
>
> Then you definitely need the column specification to correctly re-create the view.
>
> Mike Tefft
>
> From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Fergal Taheny
> Sent: Tuesday, April 25, 2017 4:47 PM
> To: Powell, Mark <mark.powell2_at_dxc.com <mailto:mark.powell2_at_dxc.com>>
> Cc: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk <mailto:woodwardinformatics_at_strychnine.co.uk>>; jeff.d.smith_at_oracle.com <mailto:jeff.d.smith_at_oracle.com>; oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> Subject: Re: SQL Developer - view DDL Formatting
>
> Hi Mark
>
> > I am of the opinion that views should always have an attribute list since I have encountered views where the view column name does not match up to the select list column names.
>
> I have a different opinion on this. If I want the column names to differ I use an alias in the select statement. The problem I see with the attribute list is that it can get out of sync with the attributes in the select list. I just dont see any good reason to use the attribute list.
>
> >Nor should a new column added to a table automatically appear in any views defined on the table. The sensitivity of the column data has to be considered in relation to the purpose and audience of each view that references the table.
>
> Agreed. I didn't say anything about automatically adding columns to views.
>
> > When the view is updated you can easily generate the view code from the RDBMS Dictionary or using DBMS_METADATA.
>
> Yes thanks but I was just trying to figure out if SQL Developer Database export could do this for me.
>
> Regards,
> Fergal
>

--
http://www.freelists.org/webpage/oracle-l