Jump to content


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


- - - - -

ORA-01841 (full) year must be not be 0

Started by Guest_Joseph V.G. Orendain_* , Apr 26 2005 08:54 PM

9 replies to this topic

#1 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 26 April 2005 - 08:54 PM

Good Afternoon,

I am getting the following error in VIP when trying to run my function

ORA-01841 (full) year must be between -4713 and +9999, and not be 0.

Once I encountered above error, all of my functions will always fail. In order to avoid the error, I have to exit out of the my ProIV session and open a new one. This is weird because, it only happens on certain instance of a session. There are times when I run this on a new session using the same test function and run it over and over again, I get the erro after 2 runs. On other session, I dont get the error at all - considering I am processing the same test data and same test function.

The same thing is happening when I try to call this test function from a task. First, the task will work. After it hit the above error, all task run will error out unless I kill the "pro .../SESSION" kernel and start a new task kernel.

Am I missing some VIP setting that is causing my ORACLE to fail? Please advise.

We noticed this error in GLOVIA if a data field has a null value and read by ProIV. But how come it is not happening a lot.....Not sure what is triggering the error.


Thanks in advance for any advice.

Joseph :x:

#2 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 420 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 27 April 2005 - 10:02 AM

I gather you have defined your field as having a field type of DATE ?

Are you doing an insert into and using TO_DATE or letting proiv do the writing ? Or how is your date field being setup ?

Can't think of anything off hand. You are going through as the same Oracle user right ?

#3 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 27 April 2005 - 01:01 PM

I've run into this before, where I was using the oracle TO_DATE function to convert a year of zero in full function sql, ie min year of range is zero, max is 9999.

I now make sure that my 'from' year is always at least 1, to avoid this error.
I never had it kill my subsequent sessions though, I was always able to recover and carry on.

#4 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 27 April 2005 - 01:09 PM

Thanks to all those who replied.

I am not using any ORACLE commands in the function. I am just reading a table wherein the last key of the file is a date field. I am issuing a sel-only command on the key before the date field.

The weird part is, it is not consistent. If I run the same function all over again, there are times I will hit the error after 5, 2, 7 tries or a session will be working ok. To correct the error, all I have to do it log out and create a new session. I am not sure if there is a VIP specific variable that needs to be set for date processing....But all processing is done in ProIV. :x:


Thanks!

Joseph

#5 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 27 April 2005 - 04:55 PM

Hello All,

I think I found the problem and have corrected it. I am in the process of testing it further...if everything goes well, I will update this post.......

The kernel we are using is 5.5 r345. The process that I imported from another kernel (GLOVIA's) seemed to not work ok on this other kernel. In the LU, there is one file whose last key is a numeric (date). The logic in the DefLogic uses SEL-ONLY. When I converted the command to use SQL - ENDSQL, I did not get the error above.......I think the SEL-ONLY does not work consistently, on some kernels, if there is a numeric key on the table...... :D

#6 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 707 posts
  • Gender:Not Telling
  • Location:Rural France

Posted 27 April 2005 - 06:26 PM

Joseph,

There's a "known bug" that was discussed a while ago on this forum that could be relevant to your problem.

When you do a SEL-ONLY on a key field that is not the last key field, ProIV can generate a spurious BETWEEN clause on the key field following the field in the SEL-ONLY (ie. a spurious BETWEEN on the last key field in your case)

(This also applies to a SEL-PARTIAL where the partial value is in fact the full length of the key field - ie. same effect as a SEL-ONLY would have)

The spurious BETWEEN is annoying but (I think) harmless provided ProIV also provides appropriate, legal 'low' and 'high' values that do not restrict the rows selected.

For ProIV to provide such "appropriate, legal" values is has to know the type of the SQL column from the EXTERNAL TYPE in the filedef.

So, I might expect to see the problem you describe if/when your final NK field did not have DATE as the EXTERNAL TYPE.

Is it possible the EXTERNAL TYPE is/was incorrect in one of the environments where you are genning but correct in another?
Otherwise, I would expect this stuff to work - you should not have needed explicit SQL.

I'd be interested if you have time to check that because otherwise it may be something I need to be worried about..
Nothing's as simple as you think

#7 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 27 April 2005 - 09:13 PM

My ORACLE error problem was fixed after using an explicit SQL statement.

Richard, I double checked our table definitions and the NK fields are all setup as DATE field. Also, fyi, when I enabled proiv tracing, it showed in the trace log that ProIV is using the values of "18000101' and '21391231' (I think) when I used the SEL-ONLY command. But I still get the error from time to time. Maybe that explains, why sometimes, it works, sometimes it doesn't. :(

After using an explicit SQL statement, I did not get the error anymore - even after running my function for 272 items! Unlike before, after processing 7-10 items I get the error immediately.

Another thing that I found out in this VIP environment is, when you passed a system variable (i.e. @DATE) in a global update parameter field, it does not register the value at first pass. I have to use a scratch variable in order to pass the value and used it the first time...weird...

I will test further tomorrow to make sure my tas, VIP function are still working ok :D whew!

Thanks!

Joseph

#8 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 28 April 2005 - 09:39 AM

Richard, I double checked our table definitions and the NK fields are all setup as DATE field. Also, fyi, when I enabled proiv tracing, it showed in the trace log that ProIV is using the values of "18000101' and '21391231' (I think) when I used the SEL-ONLY command. But I still get the error from time to time. Maybe that explains, why sometimes, it works, sometimes it doesn't

Thanks for your trouble Joseph. Those sort of trace values are certainly what I'd expect to see.

As a matter of interest, are you saying that the trace was still showing '18000101' and '21391231' even when the error actually occurred?

If so, that might well suggest some internal memory or pointer problem in the kernel. Which would explain why none of your functions work from then on and you have to get out of ProIV..

#9 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 28 April 2005 - 02:53 PM

Yes. the trace is still showing a value of '18000101' and I am still getting the error.......

#10 Donald Miller

Donald Miller

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 205 posts
  • Gender:Male
  • Location:Cupar, Fife, Scotland
  • Interests:Motorcycling, Running, Cooking

Posted 29 April 2005 - 08:39 PM

Joseph

Is there a reason why you must pass the @DATE system variable in a parameter list ?

If it is a system variable then it should be available within a global function or am I missing something ?
Half of what he said meant something else, and the other half didn't mean anytthing at all
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