Re: Strange error when calling procedure: ORA-06502 and -2147467259(80004005)

"Paul Dixon" <paul.gp.dixon_at_bt.tinnedham.com> wrote in message news:agjo73$42g$1_at_pheidippides.axion.bt.co.uk...
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D2C7FD3.A95DE063_at_exesolutions.com...
> > Marty Will Follow wrote:
> >
> > > Hello,
> > >
> > > I encounter a strange error when I try to call a stored procedure from
> > > a Visual Basic application. After calling the procedure I get the
> > > following error:
> > >
> > > Run-time error '-2147467259(80004005)':
> > >
> > > [Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error
> > > ORA-06512: at "SUSHI.SUSHI_HI_NI", line 153
> > > ORA-06512: at line 1
> > >
> > > Now, AFAIK, the run-time error is a Microsoft (VB) error while the
> > > second is an Oracle error, correct?
> > > I get this error when I use my laptop
> > > System:
> > > Microsoft Windows 2000
> > > 5.00.2195
> > > Service Pack 2
> > >
> > > Computer:
> > > x86 Family Model 8 Stepping 3
> > > AT/AT COMPATIBLE
> > > 130.544 KB RAM
> > >
> > > The Oracle server is 8i Enterprise Edition Release 8.1.7.0.0 -
> > > Production
> > >
> > > Now comes the strange part. When run the same application on a user
> > > desktop of my client, then the procedure runs OK! No errors,
> > > whatsoever.
> > >
> > > Desktop computer information:
> > > System:
> > > Microsoft Windows NT
> > > 4.00.1381
> > > IE 5.5.50.4522.1800
> > >
> > > Computer:
> > > x86 Family 6 Model 8 Stepping 3
> > > AT/AT COMPATIBLE
> > > 260.404 KB RAM
> > >
> > > I've tried all I could localising the error, but I cannot find
> > > anything. The ORA-06502 would suggest that a variable in the procedure
> > > is not big enough for a certain value, but that does not explain why
> > > it works on other computers. Besides, AFAIK, the variables are big
> > > enough to hold the information (each defined variable has room for 100
> > > characters).
> > >
> > > Oh, and as a final touch to the mystery, when I directly call the
> > > procedure in SQL Plus from my laptop (and enter the same values as I
> > > do when I use the VB application) the procedure again works. I really
> > > don't know what's happening here!
> > >
> > > I hope the information presented here is clear enough. I personally
> > > have a lot of difficulty to get any grip on the situation. Hopefully
> > > you can help me out.
> > >
> > > C ya!
> > >
> > > Marty
> >
> > My guess is that you should look at the parameters being passed to the
> > proc. You are apparently using the correct data type and size in
SQL*Plus
> > and whether it is your VB, or your ODBC, something different is coming
> > across from your app.
> >
> > Daniel Morgan
> >
>
> Marty,
>
> as the problem is specific to one client, it would also be worth checking
> that the client settings e.g. NLS environment aren't causing your
problems.
> These can unexpected behaviour with TO_CHAR, TO_DATE and TO_NUMBER and may
> be the cause of your error message.
>
> Paul Dixon
>
>

Yes, that's right. Look at the client NLS settings, as well as the Windows regional settings. I've seen issues like this where (for example) the Windows long date format doesn't match what's expected, and the VB code isn't allowing for that.

My advice in these circumstances (if that turns out to be the case) is to check it in the VB code, then always use the to_date() function explicitly.

Good luck.
Paul Received on Fri Jul 12 2002 - 01:45:18 CDT