Hi,
I am getting a table dump using an SQLplus client. The code is mentioned below.
The problem is, the value of any varchar field of the dump table is saved in the dump file with padded space.
means, if f1 is of varchar2(6) type field in a table. It has a value as "API" in the table. But when we took a dump of that table, the same field value is saved as "API " in the dump file.
code snapshot
******************************
sqlplus -s ${DB_USERNAME}/${DB_PASSWORD}@'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = '"${CONN_STR}"')(PORT = '"${CONN_PORT}"'))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = '"${DB_NAME}"')))' << !EOF!
--/*************************/--
set echo off
set feedback off
set linesize 4000
set newpage NONE
set pagesize 0
set serveroutput off
set termout off
set flush off
set NUMWIDTH 128
set COLSEP ","
set markup csv on
set trimspool on
set sqlprompt ''
set long 9999999
set VERIFY OFF
set TERM OFF
set headsep off
set TRIMOUT ON
--/*******************************/--
--/**** Clear Screen & Buffer ****/--
--/*******************************/--
--/********clear scree***********************/--
clear screen
--/*****************************/--
--/**** Start spooling file ****/--
--/*****************************/--
---/*column record_count format 9999999*/--
set heading off
SPOOL $FILE_COUNT_FILE
select count(*) as record_count from ${SCHEMA_NAME}.$TABLE_NAME;
SPOOL OFF
set heading on
set pagesize 0 embedded on
SPOOL $FILE
select * from ${SCHEMA_NAME}.$TABLE_NAME;
SPOOL OFF
EXIT
!EOF!
******************************
The dump file snapshot is
******************************
"RATE_PLAN_CD","EFFECTIVE_DATE","SYS_CREATION_DATE","SYS_UPDATE_DATE","OPERATOR_ID","APPLICATION_ID","DL_SERVICE_CODE","DL_UPDATE_STAMP","BUNDLE_VALUE","DWN_RND_CP_IND","TIER_CHRG_IND","TENANT_CD","EXPIRATION_DATE"
"SO_PAYG_DFL_ALL_1911","01-JAN-09","21-JAN-16",,460,,"APIĀ ",,0,"N","FL","M2M","31-DEC-00"
"SI_MT_PAYG_ALL_1793","01-JAN-09","21-JAN-16",,460,,"APIĀ ",,0,"N","FL","M2M","31-DEC-00"
******************************
Pls guide, how to solve it.
Most likely someone has stored that value with trailing spaces. You can verify this with:
select f1, length(f1), dump(f1) from your_table
To fix it for the CSV file, simply rtrim it, ie
select col1, col2, rtrim(f1) from your_table