Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customer
(
    ID NUMBER(6,0) CONSTRAINT customer_id_pk PRIMARY KEY
);
INSERT INTO customer ( id )
SELECT 1 FROM DUAL UNION ALL
SELECT 123456 FROM DUAL;
2 rows affected

SELECT 'C' || TO_CHAR( id, 'FM000000' ) AS id
FROM   customer;


ID


C000001

C123456


ALTER TABLE customer ADD (
  FORMATTED_ID VARCHAR2(7)
               GENERATED ALWAYS AS ( 'C' || CAST( TO_CHAR( id, 'FM000000' ) AS VARCHAR(6) ) )
);
SELECT * FROM customer;


ID
FORMATTED_ID


1
C000001

123456
C123456