Re: Would be really nice if...

<fitzjarrell_at_cox.net> wrote in message
news:1109223945.872553.321020_at_g14g2000cwa.googlegroups.com...
>
> Haximus wrote:
>> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
>> news:1109201461.566632_at_yasure...
>> > Haximus wrote:
>> >
>> >> ...if there was a select_list:: feature to do wildcard selects on
>> >> columns, then EXCLUDE specified columns, e.g.:
>> >>
>> >> SELECT * EXCEPT UNIT_NO, ETC, ETC ...
>> >>
>> >> Oracle, will ya get on this right away!!!!
>> >>
>> >> ;)
>> >
>> > Sounds like a desire for disaster to me. Consider the following:
>> >
>> > CREATE TABLE t (
>> > col1 NUMBER,
>> > col2 NUMBER,
>> > col3 NUMBER);
>> >
>> > INSERT INTO t VALUES (1,1,1);
>> > COMMIT;
>> >
>> > SELECT * FROM t EXCEPT col2;
>> >
>> > ALTER TABLE t
>> > ADD (col4 NUMBER);
>> >
>> > SELECT * FROM t EXCEPT col2;
>> >
>> > That ought to break just about everything except SQL*Plus.
>>
>> Not sure I follow...
>>
>
> No, I guess you wouldn't, having given us this wonderful 'feature
> request'.
>
>> My suggestion would be to make it a select list option, not a clause
>>
>> SELECT * EXCLUDING col2 FROM t;
>
> And obviously that is NOT the point Daniel is making. Possibly a more
> thorough explanation is in order. A table exists, of given definition:
>
> CREATE TABLE t (
> col1 NUMBER,
> col2 NUMBER,
> col3 NUMBER);
>
> Your 'wish' is granted, you write an application and code it thus:
>
> select * except col2 from t;
>
> This returns, as expected, col1 and col3, columns which the application
> is coded to accept. Fine and dandy, and all is right with the world.
> One day in the future a change is made to table t, but the application
> is left unmodified:
>
> ALTER TABLE t
> ADD (col4 NUMBER);
>
> Again your magic code is executed:
>
> select * except col2 from t;
>
> NOW the returned values are col1, col3 and col4. Of course, the
> application is not expecting col4, however it has it and has no idea
> WHAT to do with it, therefore the application breaks with the following
> errors:
>
> ERROR at line 8:
> ORA-06550: line 8, column 1:
> PLS-00394: wrong number of values in the INTO list of a FETCH statement
>
> This is one reason I question your 'request'. And this is the point
> you couldn't grasp since you couldn't follow the discussion from
> Daniel. Makes me wonder how 'forwarad' a thinker you really are, since
> you couldn't think far enough forward to predict this problem.
>
> I really see no point in making this 'feature' available.

As with any 'feature' usage is optional, you are not OBLIGATED under threat of a gun to your head to use it, if it offends your sensibilities or personal tastes. Morgan's example is really stretching it if that's the only thing he could come with... that there are considerations when modifying table structures in any situation is an IMPLIED NO-BRAINER. Using a feature suggests that you know what the hell you are doing with it in the first place... and understanding it's effects.

I'm not sure what the big conceptual problem is with it, it's simply an alternate method to specify a column select list... that's about the grand summation of it. Received on Thu Feb 24 2005 - 03:38:26 CST