This content has been marked as final. Show 4 replies
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.
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:
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.
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;
If those two UPDATES statements work with the same field (monto) why the first one is succeding and the second one not?
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;
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%' " ...
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...