Re: Select all cols in a table except for 2 explicitly specified cols without typing out the majority of the column names?
From: Ed Prochak <edprochak_at_gmail.com>
Date: Thu, 23 Jul 2009 11:20:46 -0700 (PDT)
Message-ID: <2f9e1ee3-f197-4cad-9ec0-3d5c2b83578c_at_c2g2000yqi.googlegroups.com>
On Jul 23, 6:34 am, dana <dana_at_w..._at_yahoo.com> wrote:
> Thanks Joel.
>
> > Well, are you asking for a dynamic thing or an easy way to limit what
> > other users see? If the latter, use views.
>
> Not wanting to limit what others see. I need to limit columns for my
> own queries.
>
> > 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.
>
> 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.
Date: Thu, 23 Jul 2009 11:20:46 -0700 (PDT)
Message-ID: <2f9e1ee3-f197-4cad-9ec0-3d5c2b83578c_at_c2g2000yqi.googlegroups.com>
On Jul 23, 6:34 am, dana <dana_at_w..._at_yahoo.com> wrote:
> Thanks Joel.
>
> > Well, are you asking for a dynamic thing or an easy way to limit what
> > other users see? If the latter, use views.
>
> Not wanting to limit what others see. I need to limit columns for my
> own queries.
>
> > 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.
>
> 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.
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.
> Or if someone already has this, please
> share. Or is an expert PL/SQL coder with some time to kill, please
> help. :-)
>
> Thanks.
>
> Dana
You should not have to take more than about 20minutes for one table. (assuming you have a decent editor and SQL*Plus handy).
Ed Received on Thu Jul 23 2009 - 13:20:46 CDT