Specify column width from within a query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Specify column width from within a query

Hybrid View

  1. #1
    Join Date
    Dec 2007
    Posts
    16

    Specify column width from within a query

    Hi,

    I was wondering if there is a way to dynamically specify the width of a column that is output by a query, from within that query. Similar to the way you can give a column an alias.

    I don't mean doing the "COLUMN FORMAT A??" command.

    Can't seem to find an answer to this anywhere.

    Cheers

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you can pad it with spaces if that is what you want

  3. #3
    Join Date
    Dec 2007
    Posts
    16
    I guess that would work, it's a bit crude though.

    Thanks anyway

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Crude ?? :-)

    if you can post a small example of what you want then one of us here might help.

    regards
    Hrishy

  5. #5
    Join Date
    Dec 2007
    Posts
    16
    Hi again,

    Here is an example of the problem I'm trying to solve

    If i run this query:
    Code:
    select tablespace_name, file_name
    from dba_data_files
    order by tablespace_name;
    I can adjust the column widths output using the "COLUMN FORMAT A??" command

    However if I run the query with column aliases:
    Code:
    select tablespace_name "Tablespace Name" , file_name "Data File"
    from dba_data_files
    order by tablespace_name;
    All the column formatting I set is ignored and I have very wide columns and an ugly text wrapped table.

    How can i get the second query to output the column widths i want?

    Thanks

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    How about

    Code:
    SQL> set pagesize 500
    SQL> column "Tablespace Name" format a30
    SQL> column "Data File" format a50
    SQL> l
      1  select tablespace_name "Tablespace Name" , file_name "Data File"
      2  from dba_data_files
      3* order by tablespace_name
    SQL> /
    regards
    Hrishy

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Crude? There is a function to do it, research rpad()
    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.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    format your column aliases

    column "Tablespace Name" format aX
    column "Data File" format aX

  9. #9
    Join Date
    Dec 2007
    Posts
    16
    Thanks hrishy and davey23uk, i had no idea a column alias could have it's width formatted like that.

    Brilliant, thanks!

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