Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    USA
    Posts
    1

    Unanswered: Shell Script to Convert Table to Flat file

    Hi All,

    I want to write a unix script to convert a table to a flat file with a pipe delimiter. I have 25 tables and i want to write a single shell script so that i can convert the 25 tables into 25 flat files.

    i wrote the follwoing for a single table to single flat file. can anyone make this file so that it can take 25 tables to 25 flat files(differnt)

    SCRIPTOUTPUT=$TMPPATH/$test.txt
    cat /dev/null>$SCRIPTOUTPUT
    sqlplus -s / << EOF
    -- Blank Line --
    -- Blank Line --
    whenever sqlerror exit sql.sqlcode;
    whenever oserror exit failure;
    spool $SCRIPTOUTPUT;
    set heading off
    set echo off
    select col1 '|' col2 '|' from table1;
    exit;
    EOF

    thanks
    kiran

  2. #2
    Join Date
    Dec 2003
    Posts
    56

    Re: Shell Script to Convert Table to Flat file

    How about using a for loop? Something along the lines of:

    Code:
    SCRIPTOUTPUT=$TMPPATH/$test.txt
    cat /dev/null>$SCRIPTOUTPUT
    for i in tablename1 tablename2 tablename3
    do
      sqlplus -s / << EOF
      -- Blank Line --
      -- Blank Line --
      whenever sqlerror exit sql.sqlcode;
      whenever oserror exit failure;
      spool $SCRIPTOUTPUT;
      set heading off
      set echo off
      select col1 '|' col2 '|' from $i;
      exit;
      EOF
    done
    Something along those lines is what we use at work (not there atm so can't check complete syntax/script for ya).

  3. #3
    Join Date
    Sep 2003
    Posts
    71
    // this is very similar to previous solution, jsut using WHILE loop

    you may create a file (table_file) that has entries for all your 25 tables
    and then use it while loop.



    while read aTable;
    do
    sqlplus -s / << EOF
    -- Blank Line --
    -- Blank Line --
    whenever sqlerror exit sql.sqlcode;
    whenever oserror exit failure;
    spool $SCRIPTOUTPUT;
    set heading off
    set echo off
    select col1 '|' col2 '|' from $aTable;
    exit;
    EOF
    done < table_file;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •