Why am I getting spaces when there is none??!!

Shell Programming and Scripting
1

param_data=`sqlplus -S $USER/$PASSWRD@$SCHEMA << EOF

SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON

VARIABLE param_data VARCHAR2(1000);

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

SELECT INTERFACE_ID || '|' || PARAM_FILE || '|' || SESSION_NAME || '|' || PARAM_NAME || '|' || PARAM_VALUE || '|' || TYPE || '\n' INTO :param_data
FROM CTL_INFA_PARAM
WHERE INTERFACE_ID='$INTRFC_ID' AND PARAM_FILE='$param_f';

EXIT;
EOF`

# write the parameter data in the temp parameter file
echo $param_data > $paramfile

The above script will connect to Oracle, select a a srtring and put's it into a file, the problem I'm facing is that some rows "strings" will have spaces "see red" in the output below, I noticed that this will happen only if the number of characters exceeds 80

The actual data does not have those spaces
I tried playing with those switches >> FEED OFF HEAD OFF TRIMS ON << but no go.

Please HELP.

11|AZR_ACTVTY_IN.param|s_COST_CENTER_DIM|DBConnectionODS|ODS_CAD|VAR
11|AZR_ACTVTY_IN.param|s_CTL_INTERFACE_PGM_EXEC_LOG_CUSTOMER_DIM|DBConnectionODS |ODS_CAD|VAR
11|AZR_ACTVTY_IN.param|s_CTL_INTERFACE_PGM_EXEC_LOG_CUSTOMER_DIM|INTERFACEID|11| PAR
11|AZR_ACTVTY_IN.param|s_CTL_INTERFACE_PGM_EXEC_LOG_FACT_ACTIVITY|DBConnectionOD S|ODS_CAD|VAR
11|AZR_ACTVTY_IN.param|s_CTL_INTERFACE_PGM_EXEC_LOG_FACT_ACTIVITY|INTERFACEID|11 |PAR

2

Add SET LINESIZE 1000 to your parameter list. TRIMSPOOL ON will get rid of trailing spaces. Your line is being broken up by the default LINESIZE value.

{
param_data=`sqlplus -S user/pwd@db << EOF

SET lines 10   ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON

VARIABLE param_data VARCHAR2(1000);

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

SELECT 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n'
 INTO :param_data
FROM dual;

EXIT;
EOF`
print $param_data
}
xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx 

{
param_data=`sqlplus -S user/pwd@db << EOF

SET lines 1000   ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON

VARIABLE param_data VARCHAR2(1000);

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

SELECT 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n'
 INTO :param_data
FROM dual;

EXIT;
EOF`
print $param_data
}
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3

Tmarkil-

Thanks so much for the fast reply....!

I should have looked into the news group first, the answer was there :slight_smile: