How can I alter data type and datas in it, numbers separated with comma

I have table , that has field named AMOUNT , amount takes number, table has 1.4m records, i need to upgrade them all . I would like the change NUMBER to varchar and make look like amount datas comma separated , eg: 76543-> 76,543. How can I able to do it?

Answer

i need to upgrade them all

Don’t; if you have a numeric value then store it as a NUMBER.

I would like the change NUMBER to varchar and make look like amount datas comma separated , eg: 76543-> 76,543. How can I able to do it?

Just change how you are displaying the value rather than changing how you are storing the value.

If you have the table and data:

CREATE TABLE table_name ( amount NUMBER(12,0) );
INSERT INTO table_name ( amount ) VALUES ( 76543 );

If you want to do it in a SELECT statement then use TO_CHAR and include sufficient digits to format the largest number you can hold:

SELECT amount,
       TO_CHAR(amount, 'FM999G999G999G990') AS formatted_amount
FROM   table_name;

Outputs:

AMOUNT	FORMATTED_AMOUNT
76543	76,543

If you want to do that in the table then add a virtual column:

ALTER TABLE table_name
  ADD formatted_amount VARCHAR2(16)
                       GENERATED ALWAYS AS ( TO_CHAR(amount, 'FM999G999G999G990') );

Then, after adding the virtual column:

SELECT * FROM table_name;

Outputs:

AMOUNT	FORMATTED_AMOUNT
76543	76,543

db<>fiddle here