3 Replies Latest reply: Jan 14, 2013 9:05 AM by Tridus RSS

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

    984605
      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
          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
            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
              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).