Loading a collection with check option

Lets add a new requirement to inserting a collection of data. All rows inserted should be in some range. An example of such code may be found I my earlier post load using Java

We have a collection of data. At this point we do not know the values included. A new requirement should be implemented. The upper bound of values should be checked before committing the insert. Should we browse through the collection before throwing it in?

Alex Nuijten just posted Inline View Check Option. That could be used. So the inserting method might be something like

private static void insertArray(Connection c, List elems, int upperbound) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into insert into " + 
                        " (select s, s2 " +
                        "    from si where s <= ? " +
                        "    with check option) " +
                        " select * " +
                        "    from table(?)");
        ops.setInt(1, upperbound);
        ops.setARRAY(2, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

If everything is ok there is no need to browse through the collection in client side. In invalid collection situation there is the round trip to the database to get the error "ORA-01402: view WITH CHECK OPTION where-clause violation". The List implementation for elems should be the place to handle such requirement. Just had to write something about a inline view with check option feature. Maybe some day there is similar pl/sql code that might benefit this.