As long as you have gdb connections to both you will be able to use esri (catalog) tools to move data between.
You will want to double check your CRS for support in both spatial databases.
If you don't use esri tools you will have to register your data in sde.
You simply mention that oracle supports spatial without saying that your data is in oracle spatial.
There are many things to consider if your data type is spatial (SDO) and especially if you are going to mssql-spatial.
It is possible to use both databasees without using "spatial", and unless you are developing applications and or processing huge amounts of data using the sdo functions I would suggest just using the esri binary format and allowing sde to manage the "spatial".
I am sure all of this sounds very confusing unless you already have some understanding of sde.
If not you can read more here...
ESRI Resource Center.
Edit:
When You define a geodatabase in ESRI you have several choices for the rdbms. Oracle, db2, informix, mssql. among others.
SDE creates all the tables and relationships needed to maintain the data with ESRI. There are several choices for the data type for creating tables. ESRI binary is the normal default for most of these rdbms.
Care should be taken when deciding what format to utilize as access from other applications, and interfaces can be affected by this.
ESRI resources should be able to fill in the rest of the gaps.
What about SQL Server Spatial Tools? You can incorporate it into SQL calls or use it in .NET code as well. The SqlProjection class might work for this. From the sample projection_example.sql in the source .zip:
-- Project point and linestring using Albers Equal Area projection
declare @albers Projection
set @albers = Projection::AlbersEqualArea(0, 0, 0, 60)
select @albers.Project('POINT (45 30)').ToString()
select @albers.Unproject(@albers.Project('LINESTRING (10 0, 10 10)')).ToString()
select @albers.ToString()
Also, look at this post on the SQL Server Spatial Tools discussions site, here is some code from that post:
--CREATE A GEOGRAPHIC POINT IN WGS84 LONGITUDE, LATITUDE COORDINATES
DECLARE @point GEOGRAPHY;
SET @point = GEOGRAPHY::STGeomFromText('POINT(5 52)',4326);
--PROJECT THE GEOGRAPHIC POINT TO A UNIT SPHERE MERCATOR PROJECTION
DECLARE @point_mercator GEOMETRY;
DECLARE @mercator Projection;
SET @mercator = Projection::Mercator(0) -- Initialize projection (0 = Longitude of Central Meridian)
SET @point_mercator = @mercator.Project(@point) -- Perform the projection
SELECT @point_mercator.STAsText() UNIT_SPHERE_MERCATOR
--UNIT_SPHERE_MERCATOR
--POINT (0.087266462599716474 1.0661617106056684)
--SCALE THE PROJECTED COORDINATES FROM THE UNIT SPHERE TO THE WGS84 SPHERE
DECLARE @point_mercator_wgs84 GEOMETRY;
DECLARE @a FLOAT;
SET @a = 6378137 -- Spherical Mercator Radius in meters
SET @point_mercator_wgs84 = AffineTransform::Scale(@a, @a).Apply(@point_mercator) -- perform the scaling operation
SELECT @point_mercator_wgs84.STAsText() AS WGS84_MERCATOR
--WGS84_MERCATOR
--POINT (556597.45396636787 6800125.4543973068)
To get the new transformed points to another table, you can do that with a SQL call. Actually, you could probably do all of what you need to do in one stored procedure using the SqlProjection class.
Best Answer
'Yes' is the short answer. The way you do it depends on what you want to use the data for.
The simplest way is to re-project your data into a Lat/Lon coordinate system using ST_Transform() (I think it is just 'Transform()' in SpatiaLite and something weird in Oracle). Alternatively, follow one of these recipes, perhaps using Proj4. Once you have re-projected your data you can either use it normally or pull out the sets of coordinates (it's not clear from your question what your end-result should be).
Your choice of Lat/Lon projection will depend on what you are going to do with the data next. Google uses EPSG: 900913 (Spherical Mercator) but probably the most common is WGS84 (EPSG: 4326). The EPSG code is (outside of ArcGIS) possibly the most common way of referencing your Spatial Reference System (SRS) and you can look up the codes here for whatever Lat/Lon system you need.