Results 1 to 2 of 2

Thread: Column Format

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: Column Format

    Hi all,

    Here i am having doubt in oracle SQL*PLus ....

    This is my table structure:

    Code:
    SQL> desc section
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- 
     SECTION_ID                                            NOT NULL NUMBER(8)
     COURSE_NO                                             NOT NULL NUMBER(8)
     SECTION_NO                                            NOT NULL NUMBER(3)
     START_DATE_TIME                                                DATE
     LOCATION                                                       VARCHAR2(50)
     INSTRUCTOR_ID                                         NOT NULL NUMBER(8)
     CAPACITY                                                       NUMBER(3)
     CREATED_BY                                            NOT NULL VARCHAR2(30)
     CREATED_DATE                                          NOT NULL DATE
     MODIFIED_BY                                           NOT NULL VARCHAR2(30)
     MODIFIED_DATE                                         NOT NULL DATE
    Instead of formatting all columns in a separate line, i want to format all coulmns in a single SET command....

    For example.,

    SQL> column section_id format a20
    SQL> column course_no format a20

    Here i m giving separate separate commands to format columns, instead i want all coumns to be formatted in a single command.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I know, you can not (at least, not with SET).

    I guess that your formatting is just an example, because there's no point in formatting a NUMBER(8) columns as A20.

    What you could do, in a single statement, is to format output in SELECT, such as
    Code:
    SQL> select empno, job, mgr, hiredate, sal from emp;
    
         EMPNO JOB              MGR HIREDATE        SAL
    ---------- --------- ---------- -------- ----------
          7369 CLERK           7902 17.12.80       1600
          7499 SALESMAN        7698 20.02.81       2400
          7521 SALESMAN        7698 22.02.81       2050
          7566 MANAGER         7839 02.04.81       3775
          7654 SALESMAN        7698 28.09.81       2050
          7698 MANAGER         7839 01.05.81       3650
          7782 MANAGER         7839 09.06.81       3250
          7788 ANALYST         7566 09.12.82       3800
          7839 PRESIDENT            17.11.81       5800
          7844 SALESMAN        7698 08.09.81       2300
          7876 CLERK           7788 12.01.83       1900
          7900 CLERK           7698 03.12.81       1750
          7902 ANALYST         7566 03.12.81       3800
          7934 CLERK           7782 23.12.82       2100
    
    14 rows selected.
    
    SQL> select replace(to_char(empno, '999,9'), ',', '-') empno,
      2         substr(job, 1, 3) job,
      3         replace(to_char(mgr, '999,9'), ',', '-') mgr,
      4         to_char(hiredate, 'dd.mm.yyyy') hiredate,
      5         to_char(sal, '99G990D00') sal
      6  from emp;
    
    EMPNO  JOB MGR    HIREDATE   SAL
    ------ --- ------ ---------- ----------
     736-9 CLE  790-2 17.12.1980   1.600,00
     749-9 SAL  769-8 20.02.1981   2.400,00
     752-1 SAL  769-8 22.02.1981   2.050,00
     756-6 MAN  783-9 02.04.1981   3.775,00
     765-4 SAL  769-8 28.09.1981   2.050,00
     769-8 MAN  783-9 01.05.1981   3.650,00
     778-2 MAN  783-9 09.06.1981   3.250,00
     778-8 ANA  756-6 09.12.1982   3.800,00
     783-9 PRE        17.11.1981   5.800,00
     784-4 SAL  769-8 08.09.1981   2.300,00
     787-6 CLE  778-8 12.01.1983   1.900,00
     790-0 CLE  769-8 03.12.1981   1.750,00
     790-2 ANA  756-6 03.12.1981   3.800,00
     793-4 CLE  778-2 23.12.1982   2.100,00
    
    14 rows selected.
    
    SQL>

Posting Permissions

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