This discussion is archived
3 Replies Latest reply: Jan 14, 2013 7:05 AM by Tridus RSS

strange ora-01722: invalid number problem with VB.NET Application

984605 Newbie
Currently Being Moderated
Hi,

i have a problem with my vb.net application and oracle data access. For database access I reference Oracle.DataAccess.dll in my VB.Net Project.
As Developer I've installed ODAC 11.2.0.3.20 with Oracle Developer Tools for Visual Studio.
The end user just installed ODAC 11.2.0.3.20 with Xcopy Deployment.

Now my problem:
The end user always gets ora-01722: invalid number error using the Xcopy Deployment for some queries.
Me, using ODAC with Oracle Developer Tools for Visual Studio don't get this error!

So, what ist the difference between odac and odac with Oracle Developer Tools for Visual Studio?
Does the end user really need the odac with Oracle Developer Tools for Visual Studio? I mean the end user doesen't have Visual Studio!

Why does the user with odac Xcopy Deployment installation gets ora-01722 ? Please help!
  • 1. Re: strange ora-01722: invalid number problem with VB.NET Application
    Tridus Journeyer
    Currently Being Moderated
    Can you post one of the queries that's failing, and the parameters being passed in when it fails?

    If you didn't turn on Option Strict, Vb.net does a lot of implicit conversions and some of them don't play nice with other libraries (like Oracle). So we need to rule that out before we look at something like xcopy as an issue.
  • 2. Re: strange ora-01722: invalid number problem with VB.NET Application
    984605 Newbie
    Currently Being Moderated
    Here's one of the query that fails:
      select * from Bilanzdetail where 
      abteilung like :abteilung and
      referat like :referat and
      sachgebiet like :sg and
      zeit between :startDatum and :endDatum
      order by zeit
    It's am simple query from a view.
    Parameter values are
    :abteilung = "%" (declared as varchar2(50))
    :referat = "%" (declared as varchar2(50))
    :sg = %" (declared as varchar2(50))
    :startDatum = 201201 (declared as number)
    :endDatum = 201250 (declared as number)

    In Sql Developer and Visual Studio no error occurs for that query. Seems everything is fine.

    Option Strict turned off. I have switched it on - no differnce.
  • 3. Re: strange ora-01722: invalid number problem with VB.NET Application
    Tridus Journeyer
    Currently Being Moderated
    Really? The last time I turned Option Strict on in a project that had it off, I got 1000 compiler errors. :D

    That looks fine, provided the two numbers are actually numbers and not strings. But that should fail pretty consistently.

    If you don't have visual studio, the only real difference between the xcopy Oracle client and the developer tools one is that the dev tools have an installer and the xcopy version doesn't. It won't install the developer tools if Visual Studio isn't on the system (in that case it just installs the instant client).

    So I'm not really sure what to tell you there.You could try changing over to the managed provider beta and eliminate the Oracle client as a potential issue (the managed client doesn't need any installation).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points