[Mark: please post only plain text, and don't top-post. I've moved your
reply to the bottom]
Mark Martin wrote:
> ----- 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.
> >
> 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 (?,?,?,?,?,?,?,?,?,?)};
Is that really your table and column names?
Anyway, I'm just guessing (since I can't see the actual table structure)
that TARGET_TABLE has one more column than SOURCE_TABLE, and that the number
of ? marks in the values list above is one less than the number of columns
in TARGET_TABLE. The extra column gets an initial value from the trigger,
right?
If that's true, you have two choices here:
1. Specifically list the columns for which you are supplying values:
insert into target_table (foo, bar, baz) values (?, ?, ?)
^^^^^^^^^^^^^^^
column list here
2. Provide an additional value in the values list. If the extra column is
the first column in the table, then do something like this:
insert into target_table value (0, ?, ?, ?)
^
extra value here
The trigger will change the 0 to the appropriate value.
The safest, though most verbose method is #1, as it makes no assumptions
about the order of columns in the table.
>
> 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;
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
