11 replies to this topic

[devnull]

Hi all

Platform: PROIV v 5.0r109 on Oracle 8.1.7

We've recently been experience a strange problem. On one of our larger tables, we get a numeric conversion error at random times - we have tracked the problem to be related to a date in the table, which has been set to 0 from PROIV (and thus appears as 31/12/1799 in the oracle table).

Strange thing is, there are many other records with the same dates, and they work fine - it only happens to particular records which have no unique traits when compared to other records that are being processed....


Once an offending record is found, the problem can be repeated ad infinitum - if the date is set to 1/1/1800, everything starts working - set it back to 31/12/1799 (even from Oracle) and the rollbacks return.

I am thinking that it must be some weird buffer overflow or PROIV/Oracle interface bug.... has anyone experienced something similar?

[Rob Donovan]

Hi,

Can you give us a little bit more info...

Like what is the column definition in ProIV, in @F.

Also, what is the column defintion in Oracle?

Rob D.

[devnull]

Sorry, yeh, I suppose that would be helpful

In PROIV, the column is a 'N'umber, and has an external type of DATE.

In oracle, the column is defined as a date obviously.

The column is actually a 10 element array, and the 10 corresponding columns in Oracle all seem fine.

Stupid thing is, this table hasnt changed in years, and we've been on this configuration for months.

[Rob Donovan]

Ah, we dont use Date fields in oracle, they are all numeric on this system.

I'm not sure, but I think I recall someone posting a post somewhere that said there could be problems with using Oracle Date fields with ProIV... but I cant remeber, sorry...

Rob D.

[Dan Shannon]

Is the variable NULLable? If not, that's probably your problem. For 0 date, PRO-IV should be saving a NULL to the database. 31/12/1799 probably causes the PRO-IV kernel all kinds of problems when it reads it out of the DB (it's not allowed...)

Set the NULLable flag in the PRO-IV filedef, make sure it's nullable in the DB.

I've used a multitude of PRO-IV systems with Oracle and DATE columns, never had a problem, but ALWAYS have set them to NULLable specifically.

HTH

Dan

[Chris Mackenzie]

Don't know if this is any help, but when our system returns data from Oracle date fields
to P4 is puts them into an ALPHA field. Also, FYI, we sometimes get an P4 error 'INVALID DATE'
pulling data from numeric Oracle columns into P4 date fields but the process doesn't stop!

(Really, I'm just trying to get my post count up to 100 so I move up to 'EXPERT' i've
always wanted to be one of those ;-) )

[Ralph Gadsby]

This error message can also be caused erroneously by the 'old' SQL layer - see the FAQ on the official PROIV website. PROIV EMEA

[Ralph Gadsby]

Correction to that URL... PROIV EMEA

For some reason I am unable to edit my previous post.

[Rob Donovan]

You can only edit your post for 5 minutes after you have posted it....

Rob D.

[Chris Mackenzie]

Ralph, did you mean the INVALID DATE or the NUMERIC conversion error?

(We are on v5.5 - with, I hope, the NEW sql layer...)

I should add that in V large jobs, we get random NUMERIC CONVERSION
too. So, we run in 8 simultaneous jobs instead of 4 and the problem
goes away. This is with P4 support at the moment.

[devnull]

Is the variable NULLable?  If not, that's probably your problem.  For 0 date, PRO-IV should be saving a NULL to the database.  31/12/1799 probably causes the PRO-IV kernel all kinds of problems when it reads it out of the DB (it's not allowed...)

For those that are interested, this is exactly what it was - we had a new PL/SQL package decide that since 31/12/1799 was PROIV's base date, its what it would insert instead of null. Just a simple bug really.

Funny how it only caused it to fail sometimes tho - only in particular circumstances for particular clients. It must cause some sort of overflow or something which only goes wrong on occasion

God I love PRO/IV

Edited by devnull, 14 July 2003 - 04:56 AM.

[Ralph Gadsby]

Chris, I meant 'Numeric conversion error ' and 'Invalid character value for cast specification' messages. I guess that it is also possible that an invalid date error could be caused, however I am not aware of any one reporting this to us.

The new SQL layer went into the 5.5r208 production release.