This is a varchar2(60) field which has extra spaces at the end.
length(fieldname) = length(rtrim(fieldname))
substr(fieldname,1,length(fieldname)) displays spaces (visible on toad and in an unix file)
substr(fieldname,1,30) displays 30 chars only. (field ends at the last char in toad as well as unix)
length(fieldname) = 30
Let's say a field's name is Functionname and it's values are:
substr(fieldname,1,length(fieldname)) shows something like this on toad:
and substr(functionname,1,22) on toad shows:
Here length(OracleplsqlProgramming) = 22
It's the same on an unix file. I can see the spaces( or nulls ??) In unix file, the line splits ....The unix file is created from a shell script. When I substring the fieldname to its length (Ex: 30) it displays normally on unix file. If I don't substring or substring it to length(fieldname), those blanks/nulls/extra chars appear, therefore splitting the line into two seperate lines on unix file
You should have mentioned originally that this is an issue with spooling SQL*Plus data to a file.
If length(fieldname) = length(rtrim(fieldname)) then you have no spaces at the end ... however they are getting into your output file, they are not contained in the actual data, as stored in the database.
How are you generating this file? Give us the exact script and SQL*Plus options.
sqlplus -s /nolog << ENDSQL
set pagesize 0;
set feedback off;
set echo off;
set verify off;
set linesize 100;
set trimspool on; # before spooling