Category Archives: Persistence

Select a bunch of values from the DB sequence at a time

Sometimes you need to select multiple values from certain sequence. Surprisingly it is not that easy to find the relevant information on how to do it in Google.
Here is the quick snippets for those who need it. In this snippet my_seq assumed to be a name of the previously created sequence (e.g. CREATE SEQUENCE my_seq) and 42 assumed to be the number of sequence values you want to pick from sequence in one turn.

For Oracle:

SELECT my_seq.nextval FROM dual CONNECT BY level <= 42

For H2 (I heavily use it for unit testing in favor to Oracle because it is fast, small and cool):

SELECT my_seq.nextval FROM system_range(1, 42)

Offtopic:
It is worth to mention that in H2 you can very easily select a sequence of natural numbers by accessing system_range function. E.g. if you want to get a set of {1, 2, 3, 4, 5, 6, 7, 8} you may just do SELECT * FROM system_range(1, 8). I never knew this function before and I'm happy for being accidentally find it.

Oracle+JDBC: how to insert value to the ANYDATA field

This is a short note about working with Oracle’s ANYDATA type, mostly for myself, but may be it will be useful to those who works with Oracle DB via JDBC.
In short: ANYDATA is a “variant” type that allows user to write arbitrary value to it including number, string, structure and so on.

So, assuming you have a table

CREATE TABLE mytable (
    myval        sys.ANYDATA
);

You can use the following SQL to insert the data to it:

INSERT INTO mytable (myval) VALUES (anydata.convertNumber(1));
INSERT INTO mytable (myval) VALUES (anydata.convertVarchar2('hi'));

The main question to me was how to do the same via JDBC. And this is pretty simple if you are aware of the Oracle’s JDBC driver domain model.
Basically Oracle guys provide you with oracle.sql.ANYDATA type that can work with the oracle.sql.Datum instances so that you can construct ANYDATA instance via oracle.sql.ANYDATA.convertDatum(oracle.sql.Datum datum) public static method.

Notice, that Oracle have wrappers for all the standard DB types including numbers and strings, and all of that wrappers, e.g. oracle.sql.NUMBER actually implement oracle.sql.Datum base.

So in JDBC you can do the following:

final ANYDATA value = ANYDATA.convertDatum(new NUMBER(1)); // throws SQLException
// here I use spring's JDBC template, but you can do the very 
// same via the standard PreparedStatement means
jdbcOperations.update("INSERT INTO mytable (myval) VALUES (?)", value);