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

[ChristianWells]

Hi Pramod,

Can you provide an XML schema dump of your feature class or a list of steps how you created the feature class?

Also, do you have any patches on your ArcGIS 10.2.1 client?

Thanks,

Christian

[PramodHarithsa1]

Hi Christian Wells ,

Product Version: 10.2.1.3497 No Patch Installed.

I have attached a sample XML Schema Dump. Its a ArcFM Feature. Polygon Geometry.

The trigger has the date format: to_date('9999/12/31 23:59:59 6:365','YYYY/MM/DD HH24:MI:SS D:DDD')

This is the exact error Message:

Error report -
SQL Error: ORA-01835: day of week conflicts with Julian date
ORA-06512: at "ADMIN.V3805_UPDATE", line 1
ORA-04088: error during execution of trigger 'ADMIN.V3805_UPDATE'
01835. 00000 -  "day of week conflicts with Julian date"
*Cause:    
*Action:

[PramodHarithsa1]

Hi Christian Wells​

does the details I provided give out any hint?

[SlavomirSipina]

i have the same problem....any news?

[PramodHarithsa1]

We manually corrected the dates and got it working. Yet to hear back from ESRI on this.

[DavidAllan3 · Accepted solution]

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