Can I connect to Oracle Spatial data sources from Standalone version or I need ArcGIS integration to work on it?
Oracle spatial data can be connected via Modelbuilder in standalone versions however using ArcMap or ArcGIS Pro is the best way to connect GIS information via oracle connection in Modelbuilder tool.
You need to make changes in oracle - spatial data using ArcSDE server, so that data can imported via Modelbuilder in correct format using spatial details.
ArcSDE is a server from ESRI that manages spatial data in an RDBMS. In other words, it is the component that provides support for enterprise geodatabases. Basically, ArcSDE is “middleware” that sits between the client application (e.g. ArcMap, WaterGEMS ModelBuilder) and a database server (e.g. Oracle), and extends the capabilities of the database server to support geospatial data (e.g. spatial data, linear networks, topology, coordinate systems) and workflows (e.g. versioning and long transactions).
If ArcSDE is configured to use the native spatial data format of Oracle (i.e. SDO_GEOMETRY) then it can be used in standalone or other platforms as well. So, the workaround also could be to generate copies of the Oracle database in ArcMap as shapefiles, and then use them in Model Builder.
From the perspective of WaterGEMS and ModelBuilder, if the source file is backed by the ArcSDE proprietary format, you would only be able to import source file data from WaterGEMS/SewerGEMS integrated with ArcMap. A good example of these type of file is the "ArcGIS Geodatabase Features" source file type. If you are working in WaterGEMS standalone, you would not find this source file type. If you open WaterGEMS for ArcMap, you would see and be able to use this source file type.
The key part is: "If the ArcSDE has an Oracle database as the back end data store, and ArcSDE has been configured to use Oracle's native geometry type (i.e. SDO_GEOMETRY), you can also use the Oracle connection in ModelBuilder to interact directly with the Oracle data, which has the benefit of being an option in any platform." In this case be sure not to sync out to an Oracle connection if it’s the back end of an ArcSDE data source.
So basically, it’s important to note that you have to change the configuration of the Oracle format to include spatial details in order to use our GIS data - Oracle connection in ModelBuilder.
Are Intermediate oracle Workspaces supported?
Oracle intermediate workspaces like 'Workspace Manager' are private workspaces or restricted within oracle, which are used to make changes within database to have multiple what-if actions. However Modelbuilder cannot interact with these oracle intermediate workspaces, rather ModelBuilder would be working against the final production data of oracle database.
Detailed background information on ArcSDE server
ArcSDE has a proprietary way of storing spatial data in an RDBMS, but it also supports the native spatial data formats of some database servers, including Oracle. Administrators can configure ArcSDE either way (use the proprietary format or the native format), and the choice has an influence on WaterGEMS model building workflows.
If ArcSDE is configured to use its proprietary spatial data format, the only way to exchange data with a WaterGEMS model is to use ModelBuilder from within an ArcMap session and when using an ArcEditor or ArcInfo license. This is because the ModelBuilder connection must be established using the “Geodatabase Features” or “Geometric Network” “Data Source type.” When connected in this fashion, ModelBuilder uses ESRI’s ArcObjects library to communicate with ArcSDE, and ArcSDE provides the services of translating the spatial data to and from its proprietary format.
If ArcSDE is configured to use the native spatial data format of Oracle (i.e. SDO_GEOMETRY), then there is another option. In addition, to connecting through the ESRI ArcSDE technology as just described, it is also possible to connect directly through the “Oracle” “Data Source type.” Since this does not require the use of ESRI’s ArcObjects library, it is possible to use this connection from any of the platforms supported by WaterGEMS (i.e. MicroStation, Stand-Alone, AutoCAD, and ArcMap), and it does not require a license of ArcEditor or ArcInfo.
Configuring ArcSDE to use native geometry may benefit other enterprise workflows as well since the data is not “locked up” in a proprietary format. Any application that reads and writes native Oracle geometry (e.g. ProjectWise Connector for Oracle) can consume or create the data. With that said, it is generally not recommended to write directly to the RDBMS underlying an ArcSDE implementation because ArcSDE may be maintaining relationships and indexes, etc for its use, and these would not be maintained if ArcSDE is not used.
Troubleshooting ModelBuilder issues and errors connecting to an Oracle database