Range of column names from table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Range of column names from table

  1. #1
    Join Date
    Apr 2010
    Posts
    12

    Range of column names from table

    Actually my requirement is i want to select particular range of columns from a table containing 200 columns..for example i want to select all columns except 70th and 80th column..is it possible to select data of that particular 198 columns without specifying all 198 column names.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Bizarre requirement. You will need to know the names of the columns. Use CTAS and then drop the two columns you don't want. Then select * from the new table.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    bizarre requirements may call for bizarre solutions... how about setting those unwanted columns to unused, select * then activate them back? LOL
    Last edited by PAVB; 04-12-2010 at 05:53 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    What version of Oracle allows you to mark an unused column as usable again?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I think it was on 8i the first time we have to recover an unused column - requires a little working knowledge of tab$ and col$ tables but it works just fine.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Apr 2010
    Posts
    12
    Quote Originally Posted by stecal View Post
    Bizarre requirement. You will need to know the names of the columns. Use CTAS and then drop the two columns you don't want. Then select * from the new table.
    Thanks for ur reply, but u don't understand what i asked,u said make that columns as unused and access the table...what abt other users if they want to access that columns and one more thing that two columns has been using for other purpose..but iam not using that columns in my program.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by jayasankar View Post
    Thanks for ur reply, but u don't understand what i asked,u said make that columns as unused and access the table...what abt other users if they want to access that columns and one more thing that two columns has been using for other purpose..but iam not using that columns in my program.

    you need to select the columns you want

  8. #8
    Join Date
    Apr 2010
    Posts
    12
    Quote Originally Posted by PAVB View Post
    I think it was on 8i the first time we have to recover an unused column - requires a little working knowledge of tab$ and col$ tables but it works just fine.
    you said it is possible to recover an unused columns...so could u please send that process to recover unused columns.

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    Marking a column unused was introduced in 9i. Instead of spending a lot of time waiting for a column to be dropped, Oracle introduced unused. Logically gone, but data still there, waiting to be dropped later when performance wouldn't be as impacted. There is no supported way of marking or setting a column usable again. Any direct edits or hacks on data dictionary tables may render your database unsupportable by Oracle. But go right ahead.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jayasankar View Post
    you said it is possible to recover an unused columns...so could u please send that process to recover unused columns.
    Please.
    I found your business requirement to be a really bizarre one so I just tried to figure out the most bizarre possible solution - I actuallly warned about it in my post.

    Do it the right way, query the columns you need, write view, create a temp table - do it the way you want but please don't enter bizarro world.

    I appologize if my post confused you or sent you in the wrong direction.
    Last edited by PAVB; 04-16-2010 at 12:23 AM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width