The best way of copying a table in oracle is by creating a database link and
Usine the CREATE TABLE AS syntax CREATE TABLE $table_name AS SELECT * from [EMAIL PROTECTED] Andy > -----Original Message----- > From: Mark Martin [mailto:[EMAIL PROTECTED] > Sent: 11 March 2004 09:22 > To: [EMAIL PROTECTED] > Subject: DBI - copying tables - a better way? > > Hi, > bin using DBI for a while for data warehousing type stuff and > up till now I've had to change data on the fly as it moves > from one database to another : > > my $sql01 = qq{SELECT FIELD01 .....FIELDn FROM > PRODUCTION_TABLE}; my $sql02 = qq{INSERT INTO DATAWAREHOUSE > VALUES (?,?,?............); my $sth = $dbh->prepare($sql01); > $sth->execute; > while (my @row = $sth->fetchrow) { $data1 = $row[0]; > $data2 = $row[1]; .........manipulate the dat with perl ..... > and so on........ > > But, now, I just want to copy one table into an exact replica > and to save on coding I want to avoid having to reference > each DB column and the contents of it per row. Here's what > I've got so far, the SELECT is working and I know pushing > onto seperate arrays is wrong (gives HASH(0x820c324) etc. > with print print "@rows";) but I'm stuck on how to take it further. > > #!/usr/bin/perl > use DBI;use DBD::Oracle; > $dbh01 = DBI->connect("dbi:Oracle:PROD_DB", "user", "pwrd"); > $dbh02 = DBI->connect("dbi:Oracle:DATA__WAREHOUSE", "user", > "pwrd"); my $sth01 = $dbh->prepare(<<SQL); SELECT * FROM PROD_DB SQL > > $sth01->execute(); > while( my $row = $sth01->fetchrow_hashref) { > foreach my $col (keys %{$row} ) { > print "$col = $row->{$col}\n"; > push(@cols,$col); > push(@rows,$row); > } > } > > my $fields = join(', ', @cols); > my $values = join(', ', @rows); > $sql02 = qq{"INSERT INTO DATA__WAREHOUSE($cols) VALUES ($rows)"}; > $sth02 = $dbh02->prepare($sql02) or die "Can't prepare SQL > statement: $DBI::errstr\n"; $sth02->execute(); > > > > ______________________________________________________________ > __________ > This e-mail has been scanned for all viruses by Star > Internet. The service is powered by MessageLabs. For more > information on a proactive anti-virus service working around > the clock, around the globe, visit: > http://www.star.net.uk > ______________________________________________________________ > __________ > __________________________ This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. The contents are confidential and may be privileged. Any views or opinions expressed are those of the sender and may not reflect the views or opinions of the company and the company accepts no liability in respect thereof. If you have received this email in error please notify [EMAIL PROTECTED] immediately. ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
