Skip to Main Content
  • Questions
  • Oracle dump into csv file have a auto padded field's vale

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, krishnendu.

Asked: February 02, 2023 - 4:09 pm UTC

Last updated: February 07, 2023 - 2:49 am UTC

Version: 18G

Viewed 100+ times

You Asked

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.

and Connor said...

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

Rating

  (1 rating)

Comments

Response to Mr. Connor's response

krishnendu Das, February 06, 2023 - 6:11 am UTC

Thanks for ur suggestion.
but my problem is, the code is generic. It will be used to take dumps for 20+ tables. So, I can't mention any field name in the select query. I have to use a generic query like "select * from <table_name>.

Is there any property to set this ltrip and rtim option in an SQLplus session?

Connor McDonald
February 07, 2023 - 2:49 am UTC

When someone passes in "select * from ..." you parse this to find the columns and then rewrite the query. Check out my "pr.sql" script on my github repo for an example.

https://github.com/connormcd/misc-scripts