On Tue, Aug 31, 2004 at 07:54:40AM -0400, Hardy Merrill wrote:
> 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 ought to update that to something like this:
my $sth = $dbh->prepare( <<" SQL" );
INSERT INTO lob_example ( lob_id, bindata, chardata )
VALUES ( lob_example_seq.nextval, :bindata, :chardata )
RETURNING lob_id INTO :lob_id
SQL
$sth->bind_param(':bindata', '');
$sth->bind_param(':chardata', '');
$sth->bind_param_inout(':lob_id', \my $lob_id);
$sth->execute;
Patches welcome.
The BEFORE trigger approach is also good and, I believe, can be
used with the "RETURNING lob_id INTO :lob_id" clause.
Tim.
> 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
>
>
>
