1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Formatting data in spool files in SQLPLUS

Discussion in 'SQL PL/SQL' started by svidotto, Sep 6, 2013.

  1. svidotto

    svidotto Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    hi!
    i'm pretty new to oracle and i'm not able to solve this problem for myself.
    how can i edit the date format and the position of the date(that one before the 'TTITLE') in a sqlplus report (the spool file)?
    thank you very much! :D
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    Changing the date format can be done by using the to_char by choosing an appropriate Date Time Format Element

    An example (if you use to_char to directly spool the result)

    Code (SQL):
    SQL> SELECT to_char(sysdate, 'DD-MON-YYYY') dateval FROM "PUBLIC".dual;

    DATEVAL
    --------------------
    06-SEP-2013


    SQL> SELECT to_char(sysdate, 'DD-MON-YYYY hh24:mi:ss') dateval FROM "PUBLIC".dual;

    DATEVAL
    -----------------------------
    06-SEP-2013 14:45:58

     
    If you rely only on the default nls_date_format used by SQL*Plus then you can change the current session's date format

    Code (SQL):
    SQL> SELECT sysdate FROM "PUBLIC".dual;

    SYSDATE
    ---------
    06-SEP-13

    SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY';

    SESSION altered.

    SQL> SELECT sysdate FROM "PUBLIC".dual;

    SYSDATE
    -----------
    06-SEP-2013

    SQL>
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To answer the other half of your question:

    You can provide a number of parameters to the TTITLE command to provide a top title to your liking. As an example see below:

    Code (SQL):
    SQL> ALTER SESSION SET nls_date_format='DD-MON-RRRR HH24:MI:SS';
     
    SESSION altered.
     
    SQL> COLUMN sysdate new_value sys_dt
    SQL> ttitle center sys_dt RIGHT 'Page: ' format 999 SQL.PNO skip center 'Sample Title'
    SQL> /
                                  06-SEP-2013 08:28:51                    Page:    1
                                      Sample Title
    SYSDATE              USERNAME                       CREATED
    -------------------- ------------------------------ --------------------
    06-SEP-2013 08:28:51 SYS                            10-MAY-2013 13:27:33
    06-SEP-2013 08:28:51 SYSTEM                         10-MAY-2013 13:27:33
    06-SEP-2013 08:28:51 OUTLN                          10-MAY-2013 13:27:34
    06-SEP-2013 08:28:51 FLOWS_FILES                    10-MAY-2013 13:46:31
    06-SEP-2013 08:28:51 MDSYS                          10-MAY-2013 13:41:32
    06-SEP-2013 08:28:51 ORDDATA                        10-MAY-2013 13:41:32
    06-SEP-2013 08:28:51 ORDSYS                         10-MAY-2013 13:41:32
    06-SEP-2013 08:28:51 ANONYMOUS                      10-MAY-2013 13:38:55
    06-SEP-2013 08:28:51 EXFSYS                         10-MAY-2013 13:38:38
     
                                  06-SEP-2013 08:28:51                    Page:    2
                                      Sample Title
    SYSDATE              USERNAME                       CREATED
    -------------------- ------------------------------ --------------------
    06-SEP-2013 08:28:51 DBSNMP                         10-MAY-2013 13:33:12
    06-SEP-2013 08:28:51 WMSYS                          10-MAY-2013 13:33:51
    06-SEP-2013 08:28:51 XDB                            10-MAY-2013 13:38:54
    06-SEP-2013 08:28:51 APPQOSSYS                      10-MAY-2013 13:33:13
    06-SEP-2013 08:28:51 ORDPLUGINS                     10-MAY-2013 13:41:32
    06-SEP-2013 08:28:51 APEX_030200                    10-MAY-2013 13:46:32
    06-SEP-2013 08:28:51 SI_INFORMTN_SCHEMA             10-MAY-2013 13:41:32
    06-SEP-2013 08:28:51 DIP                            10-MAY-2013 13:28:31
    06-SEP-2013 08:28:51 APEX_PUBLIC_USER               10-MAY-2013 13:46:32
     
                                  06-SEP-2013 08:28:51                    Page:    3
                                      Sample Title
    SYSDATE              USERNAME                       CREATED
    -------------------- ------------------------------ --------------------
    06-SEP-2013 08:28:51 ORACLE_OCM                     10-MAY-2013 13:29:03
    06-SEP-2013 08:28:51 XS$NULL                        10-MAY-2013 13:41:13
     
    20 ROWS selected.
     
    SQL>
    Please read here: http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve048.htm#SQPUG131 to see the full syntax for TTITLE.