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

Question

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.

Your @rows is empty when $placeholders gets created, perhaps it would be good to read one row, then recreate $placeholders before setting up $ora->do(...) — abiessu
– abiessu, Commented Aug 14, 2014 at 17:11

user3658506 · Accepted Answer · 2014-08-24 16:10:13Z

2

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' });

answered Aug 24, 2014 at 16:10

user3658506

6334 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Len Jaffe · Accepted Answer · 2014-08-15 14:51:15Z

1

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().

edited Aug 15, 2014 at 14:51

answered Aug 14, 2014 at 19:50

Len Jaffe

3,4941 gold badge24 silver badges30 bronze badges

Comments

Miller · Accepted Answer · 2014-08-14 20:12:26Z

0

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);
}

answered Aug 14, 2014 at 20:12

Miller

35.3k4 gold badges42 silver badges61 bronze badges

3 Comments

TonyaLepski Over a year ago

Thanks much !! there is a problem the employees table in postgres has a column with datatype of BYTEA and inserting into oracle datatype of BLOB and its choking on that, is there a way I can insert raw data of BYTEA to a BLOB in oracle using DBI - Tonya

Miller Over a year ago

I do not know as I haven't used Oracle myself. I would suggest that this is a new problem and that you should ask about it specifically.

Len Jaffe Over a year ago

Have you looked at the DBD::Oracle module docs have to say on the subject of working with Oracle LOBs? search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/…

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related