According to the documentation, the Oracle Spatial Object reader has an option to ‘Handle Multiple Spatial Columns’ (under 'Advanced’). If this parameter is set to Yes, feature geometry will be read into an aggregate. A parameter is set on the aggregate to indicate that each part of the aggregate is independent from the others, and its own geometry. Geometry parts of the aggregate are named and contain geometry according to their respective column in the table being read. You will probably want to deaggregate the geometries afterward.

As an alternative you can read attributes and one geometry column using SQLCreator or SQLExtractor.

by setting the "Handle multiple spatial column" to "yes", in the writer, the translation runs succesfully and result is what I expect.
The next step is to reproject geometries and write them in a new Oracle table. At the moment I didn't find the way to write data in the new table. The problem is that spatial columns have two different names (shape_prif; shape_orig_prif), but the Writer requires to set only one spatial column. How is it possible to write both the spatial columns?

Hi ​@emiliodepalma.

Let me know if the attached workspace helps get you over the hurdle.

The workspace was created in FME on macOS so formatting may be a little off when opened in FME on Windows (FME 2024.2).

A few years ago I researched how to create geometries with an Oracle Non-Spatial Writer. Part of this may still be of interest, like how to create a record in USER_SDO_GEOM_METADATA.

You can find the article here: 

I think your contribution will be very useful to me. Now I managed to find a way to make FME read the different geometric columns in the table. In the next few days I will complete the flow to write to the destination table. Thank you