Column width in spool file is 4 times of expected width

Question

I have a script file containing script like this.

SET ECHO OFF
SET TERM OFF
SET LINESIZE 1500
SET PAGESIZE 0
SET TRIMSPOOL ON
SET COLSEP ''  
set headsep off
SET FEEDBACK OFF
SET HEADSETTING OFF
SET SHOW OFF
SPOOL D:\employee.TXT

SELECT RPAD(FIRSTNAME,20,' '),           
RPAD(LASTNAME,20,' '),               
RPAD(COMPANY,50,' '),                
RPAD(ADDRESS1,50,' '),           
RPAD(ADDRESS2,50,' '),           
RPAD(ADDRESS3,50,' '),           
RPAD(CITY,50,' '),               
RPAD(STATE,50,' ')              
FROM EmployeeDetails;
SPOOL OFF

EXIT;

Above script is expected to generate 20 characters width of FIRSTNAME, LASTNAME etc. But this script is generating a file with 80 characters of FIRSTNAME and 80 char of LASTNAME, 200 char of COMPANY etc.

This script is working as expected in QA environment, but its not working as expected in PROD. Multiple jobs are configured to consume this format, so business is not interested to change delimiter. Can anyone suggest what is happening here.

Thank you in advance.

Kind Regards, Tester Shadow.

Above script is expected to generate 20 characters width of FIRSTNAME, LASTNAME etc. But this script is generating a file with 80 characters of FIRSTNAME and 80 char of LASTNAME, 200 char of COMPANY etc. We are using sqlplus version 12.2

Answer 1

You could alias and format the column expressions:

COLUMN FIRSTNAME FORMAT A20
COLUMN LASTNAME FORMAT A20
COLUMN COMPANY FORMAT A50
COLUMN ADDRESS1 FORMAT A50
COLUMN ADDRESS2 FORMAT A50
COLUMN ADDRESS3 FORMAT A50
COLUMN CITY FORMAT A50
COLUMN STATE FORMAT A50

SELECT RPAD(FIRSTNAME,20,' ') AS FIRSTNAME,
RPAD(LASTNAME,20,' ') AS LASTNAME,
RPAD(COMPANY,50,' ') AS COMPANY,
RPAD(ADDRESS1,50,' ') AS ADDRESS1,
RPAD(ADDRESS2,50,' ') AS ADDRESS2,
RPAD(ADDRESS3,50,' ') AS ADDRESS3,
RPAD(CITY,50,' ') AS CITY,
RPAD(STATE,50,' ') AS STATE
FROM EmployeeDetails;

Or concatenate into a single string:

SELECT RPAD(FIRSTNAME,20,' ') ||
RPAD(LASTNAME,20,' ') ||
RPAD(COMPANY,50,' ') ||
RPAD(ADDRESS1,50,' ') ||
RPAD(ADDRESS2,50,' ') ||
RPAD(ADDRESS3,50,' ') ||
RPAD(CITY,50,' ') ||
RPAD(STATE,50,' ')
FROM EmployeeDetails;

---

You can also check the NLS_LANG setting in each environment, which can affect how values stored in multibyte character sets is displayed. If those are different then you would see different results, even for the same database character set, column data type and value.

For example if I have a varchar2(10) column in a database with an AL32UTF8 character set, and set NLS_LANG to "ENGLISH_AMERICA.US7ASCII", which is a single-byte character set, then querying that table displays the column as 10 characters wide (in SQL*Plus). If I change NLS_LANG to "ENGLISH_AMERICA.AL32UTF8" and run the same query it will display as 40 characters wide.

As you seem to be using Windows you could, as the linked FAQ shows, change your setting with something like:

set NLS_LANG=ENGLISH_AMERICA.US7ASCII

But read the whole FAQ and understand what it is doing, and pick an appropriate value. With US7ASCII it won't display properly if you do have any multibyte (or non-ASCII) characters in the data. If you can find out the setting that works in some environments then you should - hopefully - be able to use that everywhere.

It may also be worth checking the character sets and data types in the different databases - though you'd hope they'd be the same between QA and prod. (One quick way is to select dump(firstname, 1016) from employeedetails fetch first 1 row only, which will show the data type and character set. Or query the data dictionary.)