4 Replies Latest reply: Sep 27, 2011 9:49 AM by 882656 RSS

    Calling stored procedure from Java raises ORA-1722 exception

    882656
      Hi,

      As the title says, when I call the stored procedure from java I got the exception ORA-1722 (invalid number) but I've checked and rechecked my procedure on SQL-Developer and It's alright it works nice and clean !

      What's happening? is there something I'm missing?

      The way I call the stored procedure is:

          public void execProcedure(String procedure) {
              CallableStatement cs;
              try {
                  cs = conn.prepareCall(procedure);
                  cs.execute();
              } catch (SQLException ex) {
                  Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
              }
          }
           
      where
          String procedure = "{call validateinfo}"
      I really need help on this, it's frustraiting me 'cause I need to finish a system but this little big problem is killing me ...

      Thanks any advice is very welcome :)
        • 1. Re: Calling stored procedure from Java raises ORA-1722 exception
          863331
          Post the SP ...

          Have you tested the SP using some DB access client?

          From "g o o g l e":
          ORA-01722:     invalid number
          Cause:     The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
          Action:     Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.



          RGV
          • 2. Re: Calling stored procedure from Java raises ORA-1722 exception
            882656
            Thanks for your help. Haven't tested the SP with a DB access client... which one do you recommend to use?

            The SP is the following:
            create or replace
            PROCEDURE validateInfo IS
              var number;
            BEGIN
              
              UPDATE temp_desarrollo
              SET status = '1';
              COMMIT;
              
              // Some string validations ...
              
              //This one just checks if the value of "monto" is a number
              //This update goes good
              UPDATE temp_desarrollo tmp
              SET cve_status = Decode(cve_status, NULL, '15', cve_status||',15')
              WHERE IsNumber(tmp.monto) = 'F';
              COMMIT;*/
              
              //This one just checks if the value of "monto" is greater than 0
              //if not, change de add to cve_status an errorValue
              UPDATE temp_desarrollo tmp
              SET cve_status = Decode(cve_status, NULL, '14', cve_status||',14')
              WHERE (cve_status NOT LIKE '%15%' OR cve_status IS NULL)
              AND to_number(tmp.monto) <= 0;
              COMMIT;
            
              //More string validations
            
              EXCEPTION
                WHEN OTHERS THEN
                  var := SQLCODE;
                  insert into excepciones values(to_char(var));
            
            
            END;
            I've deleted much code from the SP since I know there's nothing to do with the excepcion, just that UPDATE statement is the one giving me the exception.
            If those two UPDATES statements work with the same field (monto) why the first one is succeding and the second one not?

            Cheers,
            Federico


            P.S. the "isNumber" function is as follows, just in case.
            create or replace
            function IsNumber(val1 varchar2) return varchar2 is
              n number := null;
            begin
              n := to_number(val1,'9999999999999999.99');
                return 'T';
              exception
                when others then
                  return 'F';
            end;
            • 3. Re: Calling stored procedure from Java raises ORA-1722 exception
              863331
              Well, first and by the error "ORA-***" I assume you're using Oracle DB and for Oracle there's PLSQLDeveloper DB access client (not free :-( ), but there's also the NetBeans Services tab where you can connect to the DB (don't know if you can test a SP using NetBeans), but there's also some Oracle free clients, i guess! Try to search in oracle.com.

              Going back to your problem, my guess goes to " cve_status NOT LIKE '%15%' " ...


              RGV
              • 4. Re: Calling stored procedure from Java raises ORA-1722 exception
                882656
                I didn't find the Oracle Access Client...

                But still, I've checked and rechecked the procedure and still don't know why when I execute the procedure from SQLDeveloper works fine, but when I call the procedure from Java there are exceptions...

                Does the statements of a SP are called 100% sequentially? I mean If I launch an UPDATE and then another one, the second UPDATE executes just when UPDATE 1 has finished?

                Thanks, will be looking where the error is...


                FML