Re: Would be really nice if...

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.

>

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.

David Fitzjarrell Received on Wed Feb 23 2005 - 23:45:45 CST