How to dump /export the contents of an Oracle table into a CSV file? Let us write a shell script to dump the content of EMP table into a CSV file.
Shell Script:
#!/usr/bin/bash FILE="emp.csv" sqlplus -s scott/tiger@XE <<EOF SET PAGESIZE 50000 SET COLSEP "," SET LINESIZE 200 SET FEEDBACK OFF SPOOL $FILE SELECT * FROM EMP; SPOOL OFF EXIT EOF
Once the script is run, the file emp.csv contains the the data of the entire table. This shell script simply connects to sqlplus and fires the SELECT query to get the requisite data.
Let us try to understand the query:
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.
To know how to read and parse a CSV file, read Use awk to read a CSV file and awk to group a CSV file content.
can some one help me out, how to make filename dynamic while doing spool. e.g (MKTRPTYYYYMMDD)
ReplyDelete!#/usr/bin/perl
set pagesize 0
set lines 95
set heading off
set newpage 0
set trimspool on
-- define column seperator
set colsep |
-- suppress the line "# rows selected"
set feedback off
-- save current date in variable _Date
#set wrap off
set termout off
clear breaks
use Time::localtime;
#### Define Date and Time
$tm = localtime;
$Yday = $tm->yday;
$Year = $tm->year + 1900;
$Month = $tm->mon + 1;
#### checking for ok file
#### Correct Month and Year Day Format
if ($Month < 10) { $Month = "0$Month" };
#### Correct Month and Year Day Format
if ($Yday < 10) { $Yday = "0$Yday" };
$mydate=$Yday$Month;
print "my date:$mydate\n";
set trims on
set trim on
spool /etl/promotions/SMS_Broadcast/MKTRPT20141111.csv
select to_date(substr(tac,1,8),'YYYYMMDD'), MSISDN ||'|'|| bucket9 ||'|'|| langcur ||'|'|| credit/100 ||'|'|| ' ' from legacy.cbsdmp where date_key = to_number(to_char(sysdate,'YYYYMMDD')-1) and st
atus in ('INACT','ACTIF') and msisdn not in (select msisdn from legacy.dnd);
spool off
set termout on
exit;