Re: linux/unix script to dump csv files for each table in db
From: joel garry <joel-garry_at_home.com>
Date: Tue, 30 Nov 2010 08:51:22 -0800 (PST)
Message-ID: <cc65b858-0dc4-4815-a54d-59a30dadf607_at_y19g2000prb.googlegroups.com>
On Nov 30, 4: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
Date: Tue, 30 Nov 2010 08:51:22 -0800 (PST)
Message-ID: <cc65b858-0dc4-4815-a54d-59a30dadf607_at_y19g2000prb.googlegroups.com>
On Nov 30, 4: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
http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html
Using select * is considered bad form, because there is nothing to say the columns will be in any particular order.
Also see the documentation for sqlplus, you can set the output field separator in that tool.
There are tables to describe the structure of tables, in addition to the desc command. Search for USER_TAB_COLS at http://tahiti.oracle.com for example.
For perl fans, a bit of googling finds things like http://www.orafaq.com/usenet/comp.databases.oracle.tools/2004/08/28/0204.htm
Welcome to cdos! http://dbaoracle.net/readme-cdos.htm
jg
-- _at_home.com is bogus. http://news.techworld.com/storage/3250972/oracle-users-have-big-private-cloud-plans/Received on Tue Nov 30 2010 - 10:51:22 CST