CODEWITHSUNDEEP
perldbi
TonyaLepski
TonyaLepski

Reputation: 199

Perl/DBI - Insert rows from Postgres to an Oracle table

I am trying to write a script that will read the data from postgresql table and insert it to an oracle table, here is my script :

#!/usr/local/bin/perl

use strict;
use DBI;
use warnings FATAL => qw(all);

my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle
my @rows;
my $rows =[] ;
my $placeholders = join ", ", ("?") x @rows;

my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
 while (@rows = $sth->fetchrow_array()) {
    $ora->do("INSERT INTO employees VALUES($placeholders)");
 }

#connect to postgres
sub pgh {
my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
my $user = 'postgres';
my $pwd  = 'postgres';
my $pgh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $pgh;
}

#connect to oracle
sub ora {
my $dsn = 'dbi:Oracle:host=localhost;sid=orcl';
my $user = 'nwind';
my $pwd  = 'nwind';
my $ora = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
return $ora;
}

I am getting the following error :

DBD::Oracle::db do failed: ORA-00936: missing expression (DBD ERROR: error possibly near <*> indicator at char 29 in 'INSERT INTO employees VALUES(<*>)') [for Statement "INSERT INTO employees VALUES()"] at /usr/share/perlproj/cgi-bin/scripts/nwind_pg2ora.pl line 19.

Please help me to get my code correct. Many thanks !! Tonya.

Upvotes: 1

Views: 547

Answers (3)

user3658506
user3658506

Reputation: 633

See the documentation for DBD::Oracle you have to bind the parameter value for the BLOBs like :

use DBD::Oracle qw(:ora_types); 
$sth->bind_param($idx, $value, { ora_type=>ORA_BLOB, ora_field=>'PHOTO' });

Upvotes: 2

Len Jaffe
Len Jaffe

Reputation: 3484

my @rows;
my $rows =[] ;

my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();
while (@rows = $sth->fetchrow_array()) {
    my $placeholders = join ", ", ("?") x @rows;
    $ora->do("INSERT INTO employees VALUES($placeholders)");
}

You're joining an empty @rows to create an empty $placeholders. perform the join inside the while loop, before the do().

Upvotes: 1

Miller
Miller

Reputation: 35198

The following lazily creates a statement handle for inserting into the Oracle database based off the number of columns in the returned records.

It then inserts those column values into the database, so obviously we're assuming the table structures are identical:

use strict;
use DBI;
use warnings FATAL => qw(all);

my $pgh = pgh(); # connect to postgres
my $ora = ora(); # connect to oracle

my $sth = $pgh->prepare('SELECT * FROM "Employees"');
$sth->execute();

my $sth_insert;

while (my @cols = $sth->fetchrow_array()) {
    $sth_insert ||= do {
        my $placeholders = join ", ", ("?") x @cols;
        $ora->prepare("INSERT INTO employees VALUES ($placeholders)");
    };
    $sth_insert->execute(@cols);
}

Upvotes: 0

Related Questions