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!
4
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;
2