Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    19

    Unanswered: Display (full) column names in sqlplus

    Hi,

    I have two questions:

    1. How can we display only column names using SELECT query?
    (I am trying select * from table where 1=2, I know we can use describe table, but that's not the requirement as we are migrating code from Sybase )

    2. Is there a way to display full column names in SELECT query? Sqlplus generally display column name relative to column length as:

    Col
    ---
    AAA

    where name of column is say Column1

    Thanks in advance for any help.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    select column_name from user_tables where table_name = 'xxxx';

    You might desc user_tables and see other things you want to see

    HTH
    Gregg

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    If you are trying to generate report, you will have to explicitly state the column names and assign aliases:

    select my_column_number1 as "This is column #1" from MyTable.

    OR using COL option from SQL*Plus:

    COL COL1 HEA "This is|column #1"

    (NOTE THE '|' in the header will produce a 2 line column header.)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >You might desc user_tables and see other things you want to see
    One of which not "COLUMN_NAME"; AFAIK.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning option
    JServer Release 9.2.0.4.0 - Production

    SQL> desc user_tables
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TABLE_NAME NOT NULL VARCHAR2(30)
    TABLESPACE_NAME VARCHAR2(30)
    CLUSTER_NAME VARCHAR2(30)
    IOT_NAME VARCHAR2(30)
    PCT_FREE NUMBER
    PCT_USED NUMBER
    INI_TRANS NUMBER
    MAX_TRANS NUMBER
    INITIAL_EXTENT NUMBER
    NEXT_EXTENT NUMBER
    MIN_EXTENTS NUMBER
    MAX_EXTENTS NUMBER
    PCT_INCREASE NUMBER
    FREELISTS NUMBER
    FREELIST_GROUPS NUMBER
    LOGGING VARCHAR2(3)
    BACKED_UP VARCHAR2(1)
    NUM_ROWS NUMBER
    BLOCKS NUMBER
    EMPTY_BLOCKS NUMBER
    AVG_SPACE NUMBER
    CHAIN_CNT NUMBER
    AVG_ROW_LEN NUMBER
    AVG_SPACE_FREELIST_BLOCKS NUMBER
    NUM_FREELIST_BLOCKS NUMBER
    DEGREE VARCHAR2(10)
    INSTANCES VARCHAR2(10)
    CACHE VARCHAR2(5)
    TABLE_LOCK VARCHAR2(8)
    SAMPLE_SIZE NUMBER
    LAST_ANALYZED DATE
    PARTITIONED VARCHAR2(3)
    IOT_TYPE VARCHAR2(12)
    TEMPORARY VARCHAR2(1)
    SECONDARY VARCHAR2(1)
    NESTED VARCHAR2(3)
    BUFFER_POOL VARCHAR2(7)
    ROW_MOVEMENT VARCHAR2(8)
    GLOBAL_STATS VARCHAR2(3)
    USER_STATS VARCHAR2(3)
    DURATION VARCHAR2(15)
    SKIP_CORRUPT VARCHAR2(8)
    MONITORING VARCHAR2(3)
    CLUSTER_OWNER VARCHAR2(30)
    DEPENDENCIES VARCHAR2(8)
    COMPRESSION VARCHAR2(8)


    An alternative choice might be ALL_TAB_COLUMNS.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I apologize ... That's what I get for typing and talking at the same time !!!!
    all_tab_columns is the correct one to look at ...

    The fingers where supposed to know what the brain was thinking ... or was the brain thinking ???

  6. #6
    Join Date
    Aug 2004
    Posts
    19
    Thanks for the replies!
    I know we can specify the alias, but the problem was with select *.
    Anyway, second problem is selecting column names, I have already gave the solution of selecting from the system tables (as you have mentioned); but the code expects output from select command. Please see outputs from Sybase-isql and Oracle-sqlplus

    ===Sybase
    1> select * from att_tbl where 1=2
    2> go
    att_attco_clli att_cat att_lata att_gov_ind att_upd_usr_id
    att_upd_dt
    -------------- ------- -------- ----------- --------------
    --------------------------

    (0 rows affected)
    ===
    ===Oracle
    SQL> select * from att_tbl where 1=2;

    no rows selected

    SQL>
    ===

    any thoughts??

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > but the code expects output from select command.
    WHY does the code expect output when Oracle does NOT return lables?
    This appears to me to be a brain dead design/implementation..
    The closest I could come to a Q&D 'solution' is to modify the WHERE clause;
    WHERE 1 = 1
    AND ROWNUM < 2;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by gbrabham
    I apologize ... That's what I get for typing and talking at the same time !!!!
    all_tab_columns is the correct one to look at ...

    The fingers where supposed to know what the brain was thinking ... or was the brain thinking ???
    or:
    PHP Code:
    select column_name 
    from cols 
    where table_name 
    'TABLENAME'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Aug 2004
    Posts
    19
    >>This appears to me to be a brain dead design/implementation.. totally agree...
    Code expects only column headings... I don't know why they didn't use something like sp_help in sybase (similer to desc in oracle)???

Posting Permissions

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