oracle spool file without whitespace

Post a reply

Smilies
:icon_plusone: :like: :plusone: :gfb: :-D :) :( :-o 8O :? 8) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :wink: :!: :?: :idea: :arrow: :| :mrgreen: :angry: :baa: :biggrin:
View more smilies

BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: oracle spool file without whitespace

oracle spool file without whitespace

by jataz2 » 28/11/2019 10:22 am

So this is what i came up with: it will dump oracle data without any spaces etc between columns, while preserving the spaces within data. I thought i would share it with you.

Code: Select all

#!/usr/bin/bash
#the file where sql output will go
OUT=report.txt
>$OUT
DESC=desc.txt
>$DESC

TABLE_NAME=$1


###GET DESCRIBE####

s=""
#######################
sqlplus -s "***/***@***" << 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

desc $TABLE_NAME;

SPOOL OFF

END_SQL
#######################

for i in `cat $DESC|awk -F" " '{print $1}'|grep -v -i name|grep -v -`
do
s=$s"trim($i)||'|'||"
done

s=`echo $s|sed "s/||'|'||$//g"`
echo $s
#######################

Code: Select all

#sqlplus - silent mode
#redirect /dev/null so that output is not shown on terminal
sqlplus -s "***/***@***" << END_SQL > /dev/null

set pages 0
set feedback off
set heading off
set trimspool off
set termout off
set verify off
set colsep ""
set tab off
set lines 1500

SPOOL $OUT

select $s from $TABLE_NAME;
SPOOL OFF

END_SQL
#######################

Code: Select all

cat $OUT|sed "s/|//g"|sed "s/ *$//g" >$OUT.new
mv $OUT.new $OUT

echo Finished writing report $OUT
share

อ่านต่อได้ที่นี่ครับ https://stackoverflow.com/questions/172 ... en-columns

Top