Jump to content


Click the link below to see the new game I'm developing!


Photo
- - - - -

Weird numeric conversion error?

Started by devnull , Jul 10 2003 03:01 AM

11 replies to this topic

#1 devnull

devnull

    Newbie

  • Members
  • Pip
  • 9 posts
  • Gender:Male

Posted 10 July 2003 - 03:01 AM

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. :eek:

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

#2 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,652 posts
  • Gender:Male
  • Location:Spain

Posted 10 July 2003 - 03:06 AM

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.

Check out our game "Something Ate My Alien"

https://store.steamp...com/app/1047870

post-1-0-62401300-1554303392.jpg

https://store.steamp...com/app/1047870

#3 devnull

devnull

    Newbie

  • Members
  • Pip
  • 9 posts
  • Gender:Male

Posted 10 July 2003 - 03:15 AM

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.

#4 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,652 posts
  • Gender:Male
  • Location:Spain

Posted 10 July 2003 - 04:21 AM

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.

Check out our game "Something Ate My Alien"

https://store.steamp...com/app/1047870

post-1-0-62401300-1554303392.jpg

https://store.steamp...com/app/1047870

#5 Dan Shannon

Dan Shannon

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 374 posts
  • Gender:Male
  • Location:Australia

Posted 10 July 2003 - 11:59 AM

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

#6 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 372 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 10 July 2003 - 02:39 PM

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 ;-) )
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#7 Ralph Gadsby

Ralph Gadsby

    Expert

  • Members
  • PipPipPipPip
  • 154 posts
  • Gender:Male
  • Location:United Kingdom

Posted 10 July 2003 - 04:23 PM

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

#8 Ralph Gadsby

Ralph Gadsby

    Expert

  • Members
  • PipPipPipPip
  • 154 posts
  • Gender:Male
  • Location:United Kingdom

Posted 10 July 2003 - 04:34 PM

Correction to that URL... PROIV EMEA

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

#9 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,652 posts
  • Gender:Male
  • Location:Spain

Posted 10 July 2003 - 11:25 PM

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

Rob D.

Check out our game "Something Ate My Alien"

https://store.steamp...com/app/1047870

post-1-0-62401300-1554303392.jpg

https://store.steamp...com/app/1047870

#10 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 372 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 11 July 2003 - 07:59 AM

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.
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#11 devnull

devnull

    Newbie

  • Members
  • Pip
  • 9 posts
  • Gender:Male

Posted 14 July 2003 - 04:56 AM

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 :eek:

God I love PRO/IV :x:

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

#12 Ralph Gadsby

Ralph Gadsby

    Expert

  • Members
  • PipPipPipPip
  • 154 posts
  • Gender:Male
  • Location:United Kingdom

Posted 21 July 2003 - 01:06 PM

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.
Back to Questions

Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Click the link below to see the new game I'm developing!


  1. ProIV Resource Centre
  2. → ProIV
  3. → Questions
  4. Privacy Policy