Re: linux/unix script to dump csv files for each table in db

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 2 Dec 2010 08:03:17 -0800 (PST)
Message-ID: <dc75a379-6353-4ec8-82cb-764d87c0cbbc_at_o4g2000yqd.googlegroups.com>

On Dec 1, 1:42 pm, syd_p <sydneypue..._at_yahoo.com> wrote:
> On 1 Des, 15:52, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
> > On Nov 30, 7:45 am, syd_p <sydneypue..._at_yahoo.com> wrote:
>
> > > Hi,
>
> > > I am using a very simple script to dump out some csv files from all
> > > the tables in a mysql db.
> > > Well actually they are psv files cos they are separted by a pipe
> > > symbol.
>
> > > But anyway I want to do the same thing with oracle 10G.
> > > Any ideas on how to do this?
> > > Please help if you can!
>
> > > -Syd
>
> > > #!/bin/bash
>
> > > db=nb276
> > > user=root
> > > pass=xxxx
>
> > > for table in $(mysql -u$user -p$pass  $db -Be "SHOW tables" | sed 1d);
> > > do \
> > >  echo "exporting $table.."
> > >  mysql  $db -u$user -p$pass -e "SELECT * FROM $table" | sed 's/\t/|/g'> $db.$table.psv
>
> > > done
>
> > How do I export a database table to a flat file ?
>
> >        http://www.jlcomp.demon.co.uk/faq/flatfile.html
>
> > HTH -- Mark D Powell --
>
> Actually the question is how to export all the tables in a database to
> a flat file per table- Hide quoted text -
>
> - Show quoted text -

It is fairly easy to take the sample script and generate the csv extract script for all tables of interest though the this is only valid for tables consistenting of varchar2, number, and date data. If you have Long, raw, or LOB data types to deal with then you need another approach like Perl or Pro*c.

I guess one thing that would be important to know is what you intend to do with the data. Some data conversion could be required and you may also want to generate control cards for the loading utility at the same time you generate the extract.

HTH -- Mark D Powell -- Received on Thu Dec 02 2010 - 10:03:17 CST