In Oracle SQL (or any other dialect), if there an easy way to select
all cols in a table except for 2 explicitly specified cols, without
having to type the majority of the column names?

Example 1: Select all columns

select a.*
from all_tables a;

Example 2: Pseudo SQL to select all columns but two in a table having
many columns (e.g. 47 columns out of 49 columns for this example table
in 10g):

select all columns except for MIN_EXTENTS and MAX_EXTENTS
from all_tables a;

Is this a job for "SQL writing SQL", using PL/SQL and a limiting
subquery like:

select column_name
from all_tab_cols
where owner = 'SYS'
and table_name = 'ALL_TABLES'
and column_name not in ('MIN_EXTENTS', 'MAX_EXTENTS');

.... or is this a much simpler solution (one that doesn't involve
manually copy-pasting 47 columns)?

Thanks.

Dana

On Jul 22, 10:01*am, dana <dana_at_w... (AT) yahoo (DOT) com> wrote:
Well, are you asking for a dynamic thing or an easy way to limit what
other users see? If the latter, use views. If the former, either
play around with ..._tab_cols for the report-generator kind of thing
or keep a bunch of files around with all the columns in them and grab
what you need in your editor for the real ad-hoc stuff. Personally, I
tend to do a combination of the latter (to make scripts, especially
ETL or mass updates) and use tools that are appropriate for querying/
updating when I just need to poke around.

Other people have different requirements.

jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stori...urn/?uniontrib

Thanks Joel.

Not wanting to limit what others see. I need to limit columns for my
own queries.

Thanks for the suggestions. It's ad hoc plus from the way you're
characterized ad hoc. I don't often know in advance what X number of
columns I'll need to exclude; only know that I keep coming up against
cases where I need most, but not all, of the columns in tables with a
large number of columns.

I think I'll write some reusable PL/SQL that:

1) Prompts for the number of columns to exclude
2) Prompts for the column names to be excluded, looping the number of
times specified in step 1.

If that's doable in PL/SQL. Or if someone already has this, please
share. Or is an expert PL/SQL coder with some time to kill, please
help. :-)

Thanks.

Dana

On Jul 23, 6:34*am, dana <dana_at_w... (AT) yahoo (DOT) com> wrote:
In the long run it would probably be easier to just select the list
from dba_tab_columns into a spool file, copy/paste the file into the
select, and delete the few columns you do not want.

For your own use this is quick, simple, and effective. The technique
is also easily adapted to any table you happen to be working with.

HTH -- Mark D Powell --

On Jul 23, 6:34*am, dana <dana_at_w... (AT) yahoo (DOT) com> wrote:
Not in PL/SQL alone it isn't. (Unless you are using the web packages).
I actually would write a little PERL script if I needed this for a few
tables, or just dump the columns and fix up the query in an editor.

You should not have to take more than about 20minutes for one table.
(assuming you have a decent editor and SQL*Plus handy).

Ed