Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722: invalid number after setting user preferences
ORA-01722: invalid number after setting user preferences [message #226126] Thu, 22 March 2007 10:57 Go to next message
chinnu1208_1
Messages: 6
Registered: January 2007
Junior Member
Hi ,
I have a column called "attribute2" with datatype VARCHAR2(1000).This column has numeric values most of the times. like '0.50', '0.70', etc. and I am writing the following query in my program :

SELECT NVL (TO_NUMBER (attribute2), 0)
INTO v_freight_rate -- local variable of type NUMBER
FROM qp_price_list_lines_v qpllv
WHERE inventory_item_id = v_inv_id

In front-end when the "Number Format" is.."10,000.00"(US) this query is working fine, but when the "Number Format" is.."10.000,00"(European) the above query returns
"ORA-01722: invalid number" error.

Please suggest me how can I handle this situation in both the scenarios.,i.e., in both US and European cases without getting this error.

Thanks in advance,
Sreenivas
Re: ORA-01722: invalid number after setting user preferences [message #226128 is a reply to message #226126] Thu, 22 March 2007 11:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
This column has numeric values most of the times
If you are sure about the format, you can explicitly convert that to number using number format. Better not to use the implicit conversion.

By
Vamsi
Re: ORA-01722: invalid number after setting user preferences [message #226134 is a reply to message #226126] Thu, 22 March 2007 11:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I had a similar problem in one interface I wrote.

I created a save_to_num function to try both formats and return the one that works :

CREATE OR REPLACE FUNCTION save_to_num(INCHAR IN VARCHAR) RETURN NUMBER AS

  v_temp_number1 NUMBER(40,2);
  v_temp_number2 NUMBER(40,2);

BEGIN
  
  BEGIN 
      v_temp_number1 := To_Number(INCHAR, '999g999g999g990d00', 'nls_numeric_characters=,.');
  EXCEPTION
      WHEN OTHERS THEN
          v_temp_number1 := -1;
  END;

  BEGIN 
      v_temp_number2 := To_Number(INCHAR, '999g999g999g990d00', 'nls_numeric_characters=.,');
  EXCEPTION
      WHEN OTHERS THEN
          v_temp_number2 := -1;
  END;

  RETURN Greatest(v_temp_number1,v_temp_number2); 

END;
/



Example :

SELECT save_to_num('94,546.46') x FROM dual
UNION all
SELECT save_to_num('91.346,32') x FROM dual
UNION all
SELECT save_to_num('Broken') x FROM dual


returns
X
94546.46
91346.32
-1
Re: ORA-01722: invalid number after setting user preferences [message #226312 is a reply to message #226126] Fri, 23 March 2007 04:31 Go to previous message
chinnu1208_1
Messages: 6
Registered: January 2007
Junior Member
Thanks Thomas..Let me try your sol.
Previous Topic: execution of pl/sql code, 9i versus 10g
Next Topic: extract second (or earlier) value from dense_rank
Goto Forum:
  


Current Time: Fri Dec 09 13:47:36 CST 2016

Total time taken to generate the page: 0.17478 seconds