try $data = 'bar';$sth->prepare (insert into foo (my_id, data)
values(my_id_seq.nextval,?));$sth->execute($data);What you are doing
below is passing the string value my_id_seq.nextval to the field
my_id.
>>>Jean-Pierre Utter Lfgren <[EMAIL PROTECTED]> 08/30 10:19 pm >>>
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
