This discussion is archived
3 Replies Latest reply: Apr 11, 2013 5:00 AM by Irian RSS

SQL Dev 3.2.20: Editing view "forgets" parameters

jnp1234 Newbie
Currently Being Moderated
Editing a view like

CREATE OR REPLACE FORCE VIEW "WSSA"."VW_XXXXXX" ("X1", "X2") AS
select sysdate as tod, sysdate-1 as yest from dual;

select * from VW_XXXXXX; has columns x1 and x2.

Editing (treeview, right click edit) does not preserve the parameters:

CREATE OR REPLACE FORCE VIEW "WSSA"."VW_XXXXXX" ("TOD", "YEST_OLD") AS
select sysdate as tod, sysdate-2 as yest_old from dual;


Now select * from VW_XXXXXX; has columns tod and yest_old.
  • 1. Re: SQL Dev 3.2.20: Editing view "forgets" parameters
    rp0428 Guru
    Currently Being Moderated
    >
    Editing a view like

    CREATE OR REPLACE FORCE VIEW "WSSA"."VW_XXXXXX" ("X1", "X2") AS
    select sysdate as tod, sysdate-1 as yest from dual;

    select * from VW_XXXXXX; has columns x1 and x2.

    Editing (treeview, right click edit) does not preserve the parameters:

    CREATE OR REPLACE FORCE VIEW "WSSA"."VW_XXXXXX" ("TOD", "YEST_OLD") AS
    select sysdate as tod, sysdate-2 as yest_old from dual;

    Now select * from VW_XXXXXX; has columns tod and yest_old.
    >
    Of course it does - those are the column names!

    Views have no 'parameters'.
  • 2. Re: SQL Dev 3.2.20: Editing view "forgets" parameters
    jnp1234 Newbie
    Currently Being Moderated
    The name may not the parameters but a concept of the kind exists.

    Seems it is alias and probably should be supported by SQL Developer.

    Anyway, a bit more detail is this quote from SQL 10.2.x documentation:

    "Specify names for the expressions selected by the defining query of the view. The number of aliases must match the number of expressions selected by the view. Aliases must follow the rules for naming Oracle Database schema objects. Aliases must be unique within the view.

    If you omit the aliases, the database derives them from the columns or column aliases in the query. For this reason, you must use aliases if the query contains expressions rather than only column names. Also, you must specify aliases if the view definition includes constraints."

    Syntax:
    CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW
    [ schema. ]view
    [ (alias [ inline_constraint
    [ inline_constraint ]... ]
    | out_of_line_constraint
    [, alias [ inline_constraint
    [ inline_constraint ]... ]
    | out_of_line_constraint
    ]...
    )
    | object_view_clause
    | XMLType_view_clause
    ]
    AS subquery [ subquery_restriction_clause ] ;
  • 3. Re: SQL Dev 3.2.20: Editing view "forgets" parameters
    Irian Pro
    Currently Being Moderated
    It seems you are a little confused on the view column names and the query alias, in your example you create a view with
    CREATE OR REPLACE FORCE VIEW "WSSA"."VW_XXXXXX" ("X1", "X2") AS
    select sysdate as tod, sysdate-1 as yest from dual;
    with this you are explicitly telling the view to name it's columns "X1" and "X2", regardless of the aliases you define in the view query, the 2 columns will always be named "X1" and "X2", as the documentation says if you omit the aliases the database derives them from the query used, but in your example you are explicitly providing the aliases, so there is no need to derive them from anywhere.

    You'll get your desired behavior by creating your view with the following statement:
    CREATE OR REPLACE FORCE VIEW "WSSA"."VW_XXXXXX" AS
    select sysdate as tod, sysdate-1 as yest from dual;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points