table to csv « Różności …

Różności …

17 lipca 2014

table to csv

Zaszufladkowany do: Bazy danych,Linux,Oracle,Oracle — Tagi: — Jacek @ 18:19

#!/usr/bin/bash

FILE=”emp.csv”

sqlplus -s scott/tiger@XE <

SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 200
SET FEEDBACK OFF

SPOOL $FILE

SELECT * FROM EMP;

SPOOL OFF
EXIT
EOF

- See more at: http://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html

SET PAGESIZE 50000 => Set this to a much bigger value. This value indicates the number of lines per page. The header line will get printed in every page. In order to avoid this, set it to a bigger value so that the header appears only once.

SET COLSEP “,” => Setting the column separator to “,”. With this setting, the list displayed by the SELECT clause will be comma separated. This is the most important setting of this script.

SET LINESIZE 200 => The number of characters per line. The default is 80 which means after 80 characters, the rest of the content will be in the next line. Set this to a value which is good enough for the entire record to come in a single line.

SET FEEDBACK OFF => When a select query is executed, a statement appears at the prompt, say “25 rows selected”. In order to prevent this from appearing in the CSV file, the feedback is put off.

SPOOL $FILE => Spool command records the session queries and results into the file specified. In other words, this will write the results of the query to the file.

SELECT * FROM EMP => The query which gives the entire table contents of EMP. If only a part of the table is desired, the query can be updated to get the desired result.

SPOOL OFF => To stop writing the contents of the sql session to the file.

- See more at: http://www.theunixschool.com/2012/12/shell-script-how-to-dump-oracle-table-into-csv-file.html#sthash.XLJ9iSpO.dpuf

Brak komentarzy

Brak komentarzy.

Kanał RSS z komentarzami do tego wpisu.

Przepraszamy, możliwość dodawania komentarzy jest obecnie wyłączona.

Strona startowa: www.jaceksen.pl