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