Re: Select all cols in a table except for 2 explicitly specified cols without typing out the majority of the column names?
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 23 Jul 2009 07:13:01 -0700 (PDT)
Message-ID: <472b9b4b-a713-4c26-96e3-c881c9fa31c3_at_q11g2000yqi.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. Or if someone already has this, please
> share. Or is an expert PL/SQL coder with some time to kill, please
> help. :-)
>
> Thanks.
>
> Dana
Date: Thu, 23 Jul 2009 07:13:01 -0700 (PDT)
Message-ID: <472b9b4b-a713-4c26-96e3-c881c9fa31c3_at_q11g2000yqi.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. Or if someone already has this, please
> share. Or is an expert PL/SQL coder with some time to kill, please
> help. :-)
>
> Thanks.
>
> Dana
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 -- Received on Thu Jul 23 2009 - 09:13:01 CDT