sqloracle-databaseoracle-sqldevelopersqlcl

How to set up COLUMN HEADING in Oracle SQLcl?


I'm trying to create a series of COLUMNs with HEADING values to generate a report:

SET PAGESIZE 1;
SET LINESIZE 400;
    COLUMN C_DATEOFFSET NEW_VALUE VAL_DATEOFFSET    FORMAT A20  HEADING From_Julian;
    COLUMN C_DATEOFFSE2                             FORMAT A20  HEADING From_Date;
    COLUMN C_GLOBALNAME                             FORMAT A20  HEADING DB_Name;
    COLUMN C_OSUSER                                 FORMAT A20  HEADING OS_User;
    COLUMN C_DBUSER                                 FORMAT A20  HEADING DB_User;
    COLUMN C_TIMESTAMP                              FORMAT A40  HEADING TimeStamp;

ACCEPT MOFFSET DEFAULT '1' PROMPT 'Month Offset: ';

SELECT VARGN C_GLOBALNAME, VAROS C_OSUSER, VARDB C_DBUSER, 
        TO_CHAR(VARTS, 'YYYY-MON-DD HH24:MI TZH') C_TIMESTAMP, 
        TO_CHAR(VARDTOFF, 'YYYY-MON-DD') C_DATEOFFSE2, TO_CHAR(VARDTOFF, 'YYYYDDD') - 19E5 C_DATEOFFSET
        FROM (SELECT
            GLOBAL_NAME VARGN, UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) VAROS, USER VARDB, SYSTIMESTAMP VARTS,
            TO_DATE(TO_CHAR(SYSDATE - INTERVAL '&MOFFSET' MONTH, 'YYYYMM"01"'), 'YYYYMMDD') VARDTOFF
                FROM GLOBAL_NAME);

SET PAGESIZE 0;

My expectation was to get the results like how I see them in SQL Developer:

DB_Name              OS_User              DB_User              TimeStamp                                From_Date                     From Julian
-------------------- -------------------- -------------------- ---------------------------------------- -------------------- --------------------
JDEPD03              FELIPE.VIDAL         43CY                 2024-SEP-11 16:57 -05                    2024-AUG-01                        124214

But when I turn to SQLcl, it is not working:

C_GLOBALNAME         C_OSUSER             C_DBUSER             C_TIMESTAMP                              C_DATEOFFSE2                 C_DATEOFFSET
-------------------- -------------------- -------------------- ---------------------------------------- -------------------- --------------------
JDEPD03              FELIPE.VIDAL         43CY                 2024-SEP-11 17:02 -05                    2024-AUG-01                        124214

Any suggestions on why the Column Headings are not consistent? Any SET parameter that I might be missing?

Thanks!


Solution

  • An alternative to using SQL*Plus commands to set the headings is to specify the column aliases in the query:

    COLUMN "From_Julian" NEW_VALUE VAL_DATEOFFSET  FORMAT A20;
    COLUMN "From_Date"                             FORMAT A20;
    COLUMN "DB_Name"                               FORMAT A20;
    COLUMN "OS_User"                               FORMAT A20;
    COLUMN "DB_User"                               FORMAT A20;
    
    SELECT GLOBAL_NAME AS "DB_Name",
           UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) AS "OS_User",
           USER AS "DB_User",
           TO_CHAR(SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI TZH') AS "TimeStamp",
           TO_CHAR(
             ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -&MOFFSET),
             'YYYY-MON-DD',
             'NLS_DATE_LANGUAGE=English'
           ) AS "From_Date",
           TO_CHAR(
             ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -&MOFFSET),
             'YYYYDDD'
           ) - 1900000 AS "From_Date"
    FROM   GLOBAL_NAME;