bash ORACLE：需要导出列之间没有空格的表数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17206410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE: need to export table data without spaces between columns
提问by user2404176
Say i have Table A with columns
假设我有带有列的表 A
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
但我在多行中获得一行的输出，并带有制表符/空格
- So question is how can i fix that? and
- 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?
- 所以问题是我该如何解决这个问题？和
- 我发现了一些数据泵实用程序，如 expdp。我可以在 Unix 中使用它吗？如果是，我怎样才能以这种格式实现转储？
Thank You
谢谢你
采纳答案by user2404176
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.
所以这就是我想出的：它将转储 oracle 数据，列之间没有任何空格等，同时保留数据中的空格。我以为我会和你分享。
#!/usr/bin/bash
#the file where sql output will go
OUT=report.txt
>$OUT
DESC=desc.txt
>$DESC
TABLE_NAME=
###GET DESCRIBE####
s=""
#######################
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
desc $TABLE_NAME;
SPOOL OFF
END_SQL
#######################
for i in `cat $DESC|awk -F" " '{print }'|grep -v -i name|grep -v -`
do
s=$s"trim($i)||'|'||"
done
s=`echo $s|sed "s/||'|'||$//g"`
echo $s
#######################
#sqlplus - silent mode
#redirect /dev/null so that output is not shown on terminal
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 colsep ""
set tab off
set lines 1500
SPOOL $OUT
select $s from $TABLE_NAME;
SPOOL OFF
END_SQL
#######################
cat $OUT|sed "s/|//g"|sed "s/ *$//g" >$OUT.new
mv $OUT.new $OUT
echo Finished writing report $OUT
回答by jaypal singh
If you already have a CSV dump, then you can run the following command:
如果您已经有 CSV 转储，则可以运行以下命令：
awk 'BEGIN{FS=",";OFS=""}{=}1' csv.dump > new.dump
Untested:
未经测试：
SET HEADING OFF
SET FEEDBACK OFF
SPOOL $DESC
SELECT col1 ||''|| col2 ||''|| col3 FROM table_name;
SPOOL OFF;
回答by Dany Bee
From a "simplified oracle view" to "plain" characters with sed:
从“简化的 oracle 视图”到带有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".
说明：将第 3 行到 EOF 的所有空白（不是换行符）替换为“无”。
回答by Alex Poole
If you want the columns padded out but no additionalspaces between the columns you can do:
如果您想要填充列但列之间没有额外的空格，您可以执行以下操作：
set colsep ""
The default is to have a single space between the double-quotes, which puts a single space between the columns. You might also want to do:
默认情况下，双引号之间有一个空格，这在列之间放置一个空格。您可能还想执行以下操作：
set tab off
... to ensure that multiple spaces in the padding isn't converted to tabs, which looks fine for display but would be a pain parsing the file.
...以确保填充中的多个空格不会转换为制表符，这对于显示来说看起来不错，但解析文件会很麻烦。
If you want no spaces at all, to do this within SQL*Plus you'd need to concatenate the columns:
如果您根本不想要空格，要在 SQL*Plus 中执行此操作，您需要连接列：
select col1 || col2 || col3 || col4 from table_name;
This is useful if you're putting a delimiter between the columns (e.g. making it a CSV), but I don't know what you'd be able to do with the data in the file if you squashed everything together without delimiters.
如果您在列之间放置分隔符（例如将其设为 CSV），这将很有用，但我不知道如果您在没有分隔符的情况下将所有内容压缩在一起，您将能够对文件中的数据做什么。
