Monday, December 17, 2012

Shell Script - How to dump a Oracle table into a CSV file?



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.

1 comment:

  1. can some one help me out, how to make filename dynamic while doing spool. e.g (MKTRPTYYYYMMDD)


    !#/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;

    ReplyDelete