Thousand Seperator function in oracle? -

when executing function compiled , doesn't give errors,but when using function in select statement giving error. please me . select query this.

select numericformat(facval,5) dual;

 create or replace function numericformat   (num in decimal,    decimals in int    )  return varchar2  returnval varchar(50) ;  spdecformat varchar(20) ;  sdecformat varchar(50) ;  counter int; begin    counter := 4 ;    spdecformat :='.00';    while counter<=decimals      loop         if counter > 2         spdecformat :=spdecformat + '0';           counter :=counter+1;      end if;      end loop;      sdecformat := '###0' + spdecformat ;     returnval := to_char(num,sdecformat); return returnval; end numericformat; 

the error error report:

sql error: ora-06502: pl/sql: numeric or value error: character number conversion error ora-06512: @ "acc07.numericformat", line 21 06502. 00000 -  "pl/sql: numeric or value error%s" *cause:     *action: 

counter := 2 ;    spdecformat :='.00';      while counter<=decimals      loop       if counter > 2         spdecformat :=spdecformat+'0';           counter :=counter+1;       end if;          end loop; 

your function go infinite loop never come out of it. though compiles, doesn't mean function work fine, since issue occur @ run time.

the while condition true, , counter never increments, since function never goes if condition.

you have set counter :=2 , if condition is:

if counter > 2 then

how ever true? 2 never greater 2, therefore counter never incremented, since have inside if-end if block.

when execute function, never comes out of infinite loop.

coming requirement,

thousand seperator function in oracle?

why want reinvent wheel when oracle provides thousand separator.

from documentation,

• element : g

• example : 9g999

• description : returns in specified position group separator (the current value of nls_numeric_character parameter). can specify multiple group separators in number format model.

for example,

sql> select to_char(sal,'999g999') emp;  to_char( --------      800    1,600    1,250    2,975    1,250    2,850    2,450    3,000    5,000    1,500    1,100      950    3,000    1,300  14 rows selected. 

if want function name same of sql server function, create user-defined function in oracle database same name. logic same above query.

for example,

sql> create or replace function numericformat(   2        col number)   3      return varchar2   4      5      o_num varchar2(20);   6  begin   7      o_num:=to_char(col,'999g999');   8      return o_num;   9  end;  10  /  function created.  sql> sql> sho err no errors. sql> 

let's execute function:

sql> select numericformat(sal) emp;  numericformat(sal) ----------------------------------------------      800    1,600    1,250    2,975    1,250    2,850    2,450    3,000    5,000    1,500    1,100      950    3,000    1,300  14 rows selected.  sql>