How to change data in GEOMETRY column

First of all: if you can, change your datamodel. You are duplicating data now, which is not necessary. It’s easy enough using a view some application needs the X and Y separately in columns instead of as sdo_geometry, or even virtual columns (this being Oracle).

Secondly: That’s really easy to do in just sql:

update geo_table a set a.geometry=
(select
SDO_GEOMETRY(2001,
4326,MDSYS.sdo_point_type(b.X,b.Y, NULL),NULL, NULL)
from coord_table b
where a.id=b.id);

This of course assumes you have a column id that uniquely identifies every row. If you don’t, then you really should add one, because if you don’t have a unique identifier then you will end up either updating all rows in the table, or making the above sql a bit more complicated and less reliable/performant.

Third: if you can only use FME, and have no other options, then have a look at the VertexCreator. That will do what you want.