I'm no Oracle expert, but I've always done it the way you originally did it:
$sth->prepare ( "insert into foo (my_id, data) values
(my_id_seq.nextval, 'bar')" );
$sth->execute();
I searched for "seq" in 'perldoc DBD::Oracle' and found this under LOB locator stuff:
----------------------
my $lob_id = $dbh->selectrow_array( <<" SQL" );
SELECT lob_example_seq.nextval FROM DUAL
SQL
my $sth = $dbh->prepare( <<" SQL" );
INSERT INTO lob_example
( lob_id, bindata, chardata )
VALUES ( ?, ?, ? )
SQL
$sth->execute( $lob_id, '', '' );
--------------------
I know your question has nothing to do with LOB locators, but this could be another
way to solve the same problem. Not sure what performance impact doing a SELECT on the
sequence nextval and then INSERT'ing, but this way you can at least PREPARE your
insert just once.
HTH.
Hardy Merrill
>>> Jean-Pierre Utter Löfgren <[EMAIL PROTECTED]> 08/31/04 07:19AM >>>
Does anybody have a clue on how to bind oracle sequences.
Background:
Due to the large amount of data to be loaded into our oracle9i db by our
applications, a bulk-load software would have been our first choice to
handle the inserts. However, since our data is higly dynamic in its
construction, this is not an option.
In order to solve this, I've developed a perl package to handle the
inserts. So far so good, but now our DBAs are running wild since I
produce a fair amount of latching and statements prepares (400+/s),
chocking the production database (SUN Enterprise 4500, 14 CPU).
Every row inserted is unique, but some use the same columns to insert,
so I'd like to use $sth->bind_param or the $sth->execute($1, $2 ....)
for those, to minimize my preparing of statments.
You might think I should have user bind variables from the start, and I
tried, trust me, but the problem back then is the same as I will try to
adress now.
The Problem
....is however that I use sequences in the database inserts for various
reasons, as some inserts use the same primary key, sub-data needs to be
referenced to each other etc. Using DBI/DBD, it handles inserts using
sequneces very nicely as long as I do
$sth->prepare ( "insert into foo (my_id, data) values
(my_id_seq.nextval, 'bar')" );
$sth->execute();
But if I rearange the code to be more efficient...
$sth->prepare ( "insert into foo (my_id, data) values (?,?)" );
$my_id = "my_id_seq.nextval";
$data = "bar";
$sth->execute($my_id,$data);
or
$sth->prepare ( "insert into foo (my_id, data) values (?,?)" );
$sth->bind_param( 1, "my_id_seq.nextval" );
$sth->bind_param( 1, "bar");
$sth->execute();
, I get "ORA-01722: Invalid number" on the sequence.
I tried to find infomation on ora_types argument to indicate the
argument type in the bind, but am so far unsuccessful. I've even tried
to bind a sub-select like "(select my_id_seq.nextval from dual)", but
got the same result...
Anybody got any id.ea if this is possible or not to achieve this, i.e.
using oracle sequences in binds?
/JP
