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.

ISO year week day to date

Trying to get date out of three of numbers. Three numbers are ISO standard year, ISO standard week and number of a day in a week. The first day of a week is Monday.

select to_date ('2010 40 4','iyyy iw d') from dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"

A date specification contained an invalid format code. Only the following
may be specified when entering a date: year, month, day, hours, minutes,
seconds, Julian day, A.M./P.M. and B.C./A.D.

select to_timestamp ('2010 40 4','iyyy iw d') from dual;

Surprise, no luck, the same error.

From Oracle support formely known as metalink can be found a statement that the feature has not been in such priority to be implemented. Build your own function. I am too lazy to do that. And as I know the timerange I am interested in I use brute force. Use data. We are in a database. It is built to store data. I will use that ability.

create table isoyearweekday_to_date(
isoyearweekday number(7) constraint isoyearweekday_to_date_pk primary key
, dat date not null) 
organization index;

insert into isoyearweekday_to_date 
select to_char(d,'iyyyiwd')
     , d 
  from (
   select to_date('17991231','yyyymmdd')+level d
     from dual 
   connect by level <= to_date('22000101','yyyymmdd')-to_date('18000101','yyyymmdd')+1
  )
;

select * from isoyearweekday_to_date where isoyearweekday = 2010404
;

2010404 07.10.2010

null - quilty or not

It is Friday evening. Watching a recording of Mentalist.

true - false - null
had some drinks - sober - a drink too much
innocent - pulled the trigger - too drunk to remember