Julian date issue with Multi-versioned views

PramodHarithsa1

Hi All,

The "Instead of" triggers created on Multi - version views for Updating, Inserting and Deleting records have an issue with the date field.

An update or insert command executed using the views throws the below error,

ORA-01835: day of week conflicts with Julian date

ORA-06512: at "ADMIN.V2356_UPDATE", line 1

ORA-04088: error during execution of trigger 'ADMIN.V2356_UPDATE'

ORA-02063: preceding 3 lines from ARCGIS

The date format used in these triggers doesn't comply with the format defined in the feature class.

The trigger uses the format: 9999/12/31 23:59:59 6:365','YYYY/MM/DD HH24:MI:SS D:DDD as against the format defined for the feature class 31/12/9999 23:59:59','DD/MM/YYYY HH24:MI:SS

Is this a know issue/bug? The only way I could fix this was by replacing the date format in all the triggers.

Database used: Oracle 11g

Geodatabase version: 10.2.1

Regards,

Pramod

DavidAllan4

Hi Pramod Harithsa,

Have just spent some time very frustrated with the same issue. The problem is caused by the nls settings and the different date formats used by oracle for different parts of the world.

In the US (I guess where ESRI tested this), a value of 6 for the day represents Friday. However in the UK and elsewhere, the 6th day of the week gets translated to a Saturday. The last day of the year 9999 is a Friday, and therefore the update fails.

So you have a couple of options here:

1) alter the triggers to either remove the day of the week component or provide the correct day of the week value for your particular locale - e.g. for the UK

TO_DATE('9999/12/31 23:59:59 5:365','YYYY/MM/DD HH24:MI:SS D:DDD')

2) alter the nls_territory parameter when writing to the multiversioned view, although you'd need to be careful not to break anything else with this - e.g.

    alter session set nls_territory = 'AMERICA';

    insert into myMultiVersionedView ....

Not sure why the trigger needs to include the D:DDD component at all though - seems a bit over specific to me. ESRI should either remove the day of the week component or change the implementation of these triggers to take account of the local database settings as opposed to assuming everyone is in the US.

David