Bob,
thanks! That worked fine. Went with no. 2 . No, those are not the original
table and column names. Thanks again for takin the time.
Mark
----- Original Message -----
From: "Bob Showalter" <[EMAIL PROTECTED]>
To: "'Mark Martin'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, June 03, 2003 2:02 PM
Subject: RE: DBI and Unique Keys
> [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]
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
