Re: DBI and Unique Keys

[Mark Martin]

Bob,
here is the code with SQL embedded. It's a very simple extract from one Dbase and 
insert into another :

#!/usr/bin/perl
use CGI qw(fatalsToBrowser);
use DBI; use DBD::Oracle;

$dbh1 = DBI->connect( "dbi:Oracle:SOURCE_SID", "username", "pword" ) or die "Can't 
connect to Oracle database: $DBI::errstr\n";
$dbh2 = DBI->connect( "dbi:Oracle:TARGET_SID", "username", "pword" ) or die "Can't 
connect to Oracle database: $DBI::errstr\n";

my $sql1 = qq{SELECT FIELD1, FIELD2, FIELD3, FIELD4, ........... FIELD10 FROM 
SOURCE_TABLE}; 
my $sql2 = qq{INSERT INTO TARGET_TABLE VALUES (?,?,?,?,?,?,?,?,?,?)};

my $sth1 = $dbh1->prepare($sql1) or die "Can't prepare SQL statement: $DBI::errstr\n";
$sth1->execute or die "Can't execute SQL statement: $DBI::errstr\n";

while (my @row = $sth1->fetchrow) {
                    chomp;

                     $var1 = $row[0];
                     $var2 = $row[1];
                     .
                     .
                     .
                     $var10 = $row[9]; 

                     my $sth2 = $dbh2->prepare($sql2) or die "Can't prepare SQL 
statement: $DBI::errstr\n";
                    $sth2->execute($var1,$var2........$var10) or print "WARNING: Can't 
execute SQL statement 2: $DBI::errstr\n";
                    }
$dbh1 ->disconnect(); $dbh2 ->disconnect(); exit;

----- Original Message ----- 
From: "Bob Showalter" <[EMAIL PROTECTED]>
To: "'Mark Martin'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, June 02, 2003 2:14 PM
Subject: RE: DBI and Unique Keys


> Mark Martin wrote:
> > Hi,
> > I have an Oracle table with a Unique Key which is generated by a
> > trigger : 
> > 
> > CREATE TRIGGER MYTRIGGER
> > before insert on MYTABLE
> > for each row
> > begin
> > select MYSEQUENCE.nextval into :new.MYCOLUMNAME from dual; end;
> > And this works fine on normal insert
> > 
> > When I try to insert from a perl script I get the following :
> > ORA-00947: not enough values 
> 
> Need to see your SQL.
> 
> > 
> > When I try to add an extra placeholder for the value the will
> > be generated by the trigger I get the following : execute
> > failed: called 28 bind variables when 29 are needed
> 
> You need to supply an additional bind variable (value doesn't matter).
> Again, we need to see the code.
> 
> -- 
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>