Re: how to achieve autoincremented PKs (as datatype SERIAL does)

frank_ratzlow_at_hotmail.com (Frank Ratzlow) wrote ...
>
> INSERT INTO customer VALUES ('Sven','Grundmann');
>
> Finally, to top all this I want to execute this statment from within
> Java Code via JDBC. Is there any supported way to precalc the pk?
>

Hi Frank,

you can use sequences to generate identifiers:

     select customer_seq.nextval from sys.dual

If you do many inserts, you probably want to prefetch a block of identifiers that you can use when needed, to reduce the number of trips to go to the database to fetch new identifiers. Check out the increment option of a sequence.

Instead of doing this, you can also call a stored procedure, passing arguments. The stored procedure then fetches a new identifier from the sequence, makes the insert, then returns the identifier using an out variable. So the procedure computes a new pk and does the insert for you, all in one hit.

However, if you use a stored procedure, JDBC batch updates don't help, because Oracle's JDBC driver only improves performance for PreparedStatements, not CallableStatements.

If you want to insert multiple rows in one batch, but use a stored procedure, you may be able to pass data as an array to the stored procedure using PL/SQL netsted types and Oracle 8 and later. But I'm not that familiar with this, because we did not choose this route (we had to support Oracle 7, and therefore did not look further into this).

-Andrej Received on Thu Feb 27 2003 - 14:47:58 CST