ORACLE: need to export table data without spaces between columns

Question

Say i have Table A with columns

      col1   col2   col3   col4
      -------------------
      sajal  singh  28     IND
      hello  how    are    you

I want to export the data into a flat file without spaces or tabs between columns So the output should be

      cat dump
      sajalsingh28IND
      hellohowareyou

what i have tried. i have written a script

      #!/usr/bin/bash

      #the file where sql output will go
      OUT=report.txt
      >$OUT
      DESC=desc.txt
      >$DESC

      sqlplus -s "xxx/xxx@xxx" << END_SQL > /dev/null

      set pages 0
      set feedback off
      set heading off
      set trimspool off
      set termout off
      set verify off
      set wrap off

      SPOOL $DESC

      Select * from table_name;

      SPOOL OFF

      END_SQL

But i am getting outputs of one row in multiple lines and with tabs/spaces

1. So question is how can i fix that? and
2. I found some data pump utilities like expdp. Can i use that in Unix? if yes, how can i acheive the dump in that format?

Thank You

Answer 1

If you already have a CSV dump, then you can run the following command:

awk 'BEGIN{FS=",";OFS=""}{$1=$1}1' csv.dump > new.dump 

Untested:

SET HEADING OFF
SET FEEDBACK OFF
SPOOL $DESC

SELECT col1 ||''|| col2 ||''|| col3 FROM table_name;

SPOOL OFF;

Answer 2

From a "simplified oracle view" to "plain" characters with sed:

sed -n '3,$ s/\s//gp' file

$cat file
      col1   col2   col3   col4
      -------------------
      sajal  singh  28     IND
      hello  how    are    you
$sed -n '3,$ s/\s//gp' file
sajalsingh28IND
hellohowareyou

Explanation: replace all white space (not line breaks) from line 3 to EOF with "nothing".