Link to home
Start Free TrialLog in
Avatar of MICHAEL KEMP
MICHAEL KEMPFlag for United States of America

asked on

SELECT EXCLUDE A COLUMN

I've been reading some answers to the question "How do I select all columns from a given table, but exclude certain ones?". It appears that, outside of SQL*Plus, there is no way to do this. My question has two parts:

1) Is this true? Outside of SQL*Plus, with its "noprint" option (which is just a formatting option, not an operation option, the way see it), is it impossible?

2) Is this something that has been considered by Oracle and turned down? I encounter many situations where this would be a real time-saver/convenience-feature. I'm in the habit of exporting a dataset listing all columns from a table (ALL_TAB_COLUMNS) to Excel and then writing some VBA to create an INSERT, SELECT statement or whatever (so I don't have to sit there and type out all the columns one-by-one) just so I can exclude the one or two that I don't want. Other people just copy and paste resultset grids, which is simpler, obviously. But I mean, come on!! SAS does it, why can't Oracle? Maybe SELECT * EXCLUDE COL1,COL45,COL187 FROM MY_BIG_TABLE

How hard would that be?

I've seen a couple of comments to the effect of: "I can't imagine why that would ever be necessary.", or, "If you're a lazy typer....".

One of the most common scenarios where it would be helpful is when working with a DEVELOPMENT vs. PRODUCTION environment. We frequently are adding columns to tables in DEVELOPMENT while still needing to transfer data back and forth between the same tables in different environments.

It would be much easier if we could say:
===============================================================
CONNECT MYNAME/MYPASS@DBPROD;
INSERT INTO TABLE1
SELECT * EXCLUDE COL87,COL88,COL89 FROM TABLE1@DBLINK_DEVEL;
===============================================================

instead of
===============================================================
CONNECT MYNAME/MYPASS@DBPROD;
INSERT INTO TABLE1
     (COL01
     ,COL02
     ...
     COL86)
SELECT
     COL1
     ,COL2
     ...
     COL86
FROM
     TABLE1@DB_LINK_DEVEL
===============================================================

I'm sure I'm missing something here....and would appreciate any insight you might have.
Thank you.

-Mike
ASKER CERTIFIED SOLUTION
Avatar of anand_2000v
anand_2000v
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MICHAEL KEMP

ASKER

Sounds authoritative and definitive.

Who could ask for more in an answer?

Ah yes...one final requirement: that it be correct!! LOL....haha...I assume that it is. I have certainly never heard otherwise.

Many thanks.

It is curious that such a solution would not be available. I am a SAS neophyte of the lowest degree, and yet this is one of the first things I learned. I guess they consider it elemental, and I would be inclined to agree.

Wait for 11g it is.

Thanks again....and of course, Happy Holidays!!
HTH
Happy holidays to you too.