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);

Leave a Reply

Your email address will not be published. Required fields are marked *