Re: How to use sequences in DBI/DBD::Oracle with bind

From: Jean-Pierre Utter Löfgren (uttiponken_at_yahoo.se)
Date: 08/31/04 

To: dbi-users@perl.org
Date: Tue, 31 Aug 2004 15:08:08 +0200

Thanks for your response, Hardy!

I read that too, but since I dynamicaly build the "meta-sql" code in
another application, I haven't really control over knowing where the
sequence are located and wether the occcur in a statment or not.

Kind of tricky this one.

Thanks also Andy and Jon for your mailcontribution.

So, I think I'll have to do a hybrid of your suggestions and the present
design.

/JP

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 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 <uttiponken@yahoo.se> 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
>
>

Relevant Pages

  • RE: How to use sequences in DBI/DBD::Oracle with bind
    ... Array BioPharma, Inc. ... >> Does anybody have a clue on how to bind oracle sequences. ...
    (perl.dbi.users)
  • RE: How to use sequences in DBI/DBD::Oracle with bind
    ... Senior DBA ... Does anybody have a clue on how to bind oracle sequences. ...
    (perl.dbi.users)
  • RE: How to use sequences in DBI/DBD::Oracle with bind
    ... In most cases a trigger probably is the best. ... Subject: How to use sequences in DBI/DBD::Oracle with bind ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)
  • Re: How to use sequences in DBI/DBD::Oracle with bind
    ... > SQL ... > Does anybody have a clue on how to bind oracle sequences. ... > You might think I should have user bind variables from the start, ...
    (perl.dbi.users)
  • Re: To LinQ or not LinQ
    ... The assertion that SQL is _strictly_ set-based doesn't make sense to me either, since when you query the database, records always have to be returned in _some_ order. ... Likewise, while it's true that the LINQ syntax is very much centered around the IEnumerableinterface, there's nothing about the interface that requires the sequences to be well-defined. ... It's only practicality that causes that not to happen in practice. ...
    (microsoft.public.dotnet.languages.csharp)