Hi, I will try to simplify my problem using the below simple example on DUAL table although it happens in real application tables. The problem is that we have SQL script that should spool results in csv file. It works in like 6 environments (DB) okay, let’s call it case A, but failing in only one (case B) creating so many spaces in the file for the columns we use either NVL or TO_CHAR functions. All DBs are on the same Oracle server.
Case A (a good one!)
SELECT nvl(to_char(sysdate, 'DDMMYYYY'),' ') dualsys,'TEST111' test1111 FROM dual;
DUALSYS TEST111
-------- -------
28102023 TEST111
Case B (a wrong one!)
SELECT nvl(to_char(sysdate, 'DDMMYYYY'),' ') dualsys,'TEST111' test1111 FROM dual;
**DUALSYS ** TEST1111
--------****------------------------------------------------------------------- --------****------------------------
28102023 TEST111
You can see these additional spaces created in this ‘problematic’ DB.
We have on the beginning pf the script this:
set timing on;
set colsep ','
set pagesize
set linesize 2000
set trimspool on
set trimout on
set wrap off
set heading on
set verify off
set feedback off
set serveroutput off
set term off
set feed off
So, the results are like this from the real file:
28012023,TEST111, ← expected, a good one
The bad one, with the spaces:
28012023, ,TEST111 ,
Only differences are in Databases, but we cannot figure it out what caused the problem.
When we export schema for bad one into good one, it works fine, so all DDLs are the same.
Also, adding or changing settings inside the script, or using TRIM function on such columns did not help.
We compared DB settings, and it seems all the same.
The real problem is that we have use l_inesize 32000_ in order that such columns be written, but then the size of the file(s) increase (almost 50GB per files /as we have 9 scl like this ones/.
Any idea what should cause a problem?
Summary - NVL to column create additional spaces either the value exists or not
Thank you in advance!
Branx.