How To Access Oracle Data From FME (Feature Manipulation Engine) URL: Source: Author: Purpose: Last edit: http://gisweb1.serm.gov.sk.ca/mapserver/FME/ \\filereg1\groups\geo\ FME\docs\Access_Oracle_Data_From_FME\PDF\Access_Oracle_Data_from_FME.pdf Doug Dale-Johnson Geomatics Services Section (306) 787-4735 [email protected] Written for the FME course in Feb 2006; generally applicable for accessing Oracle datasets from FME. Wednesday, February 22, 2006 One tool for accessing various GIS-related datasets is Safe Software’s Feature Manipulation Engine (FME), for which SE holds two floating licenses. Using FME, GIS Analysts may wish to access either non-spatial datasets stored directly within an Oracle database instance or spatial datasets stored indirectly by ESRI ArcSDE within an Oracle database instance. If while using FME you wish to access attribute information directly from Oracle tables, you need to install an Oracle client and set up your Oracle-related environment variables so FME can connect to Oracle. If you wish only to access spatial datasets stored within Oracle by ArcSDE, please skip to the second part of this document, "How To Access Spatial Datasets Stored Within SDE." How To Access Non-Spatial Datasets Stored Directly Within Oracle FME can read attribute information (non-spatial datasets) from Oracle tables or views. For purposes of this document and FME software, views are handled exactly the same as tables. If you do not already have an Oracle client installed on your PC, and if you have no other requirement for the full Oracle client, the easiest method to connect FME with Oracle is the Oracle Instant Client. See your local IT Helpdesk staff to confirm that you may install this software, or to have them install it for you. In a nutshell, copy the Instant Client files to a new folder on your C: drive. For the FME class in February 2006, your Oracle Instant Client is located at C:\Oracle\instantclient_10_2. If you have any problems, http://www.fmepedia.com/index.php/Oracle_Instant_Client explains in detail how to set up your Instant Client. Once you have the Oracle Instant Client copied to your hard drive, someone with Administrator rights on your local machine (IT Helpdesk) will need to alter two environment variables: • Append the location of your Oracle Instant Client to your PATH • Set TNS_ADMIN to the location of your TNSNAMES.ORA file For your course computer setup, the TNSNAMES.ORA file has been placed into your Instant Client folder. Your TNS_ADMIN must therefore point to that folder. Note that this is a limited TNSNAMES.ORA that will likely not reference the Oracle instance you are looking for. See your IT Helpdesk or Oracle Database Administrator (DBA) for the TNSNAMES.ORA entry for the Oracle instance (database) you wish to access. You may now add Oracle tables as new datasets. Note that you will likely wish to choose format "Oracle 10g/9i/8i/8 Database (Attributes Only)". Click on the Settings button in the "Add Source Dataset". In the "Database Input Settings" dialog, input under "Database" the database name/label your DBA inserted into your TNSNAMES.ORA file. In this case, for the class example using Steve Porter's data, we are accessing the BIOT instance. Input also your User ID and Password. (Note that the FME_CLASS user will be deleted at the end of the FME training.) Under the “Constraints” section, click on the ellipsis button (...) to the right of the "Table List:" label. Note that for the demo account created for the FME class, there are a number of excess tables visible. The tables you will need to find are owned by FME_CLASS (ie: FME_CLASS.EO_FME_CLASS …). You may find these most easily if you filter by "FME_CLASS". How To Access Spatial Datasets Stored Within SDE If you wish only to access spatial datasets stored within SDE, the above setup is not required. However, you must still set the Source Dataset and settings information as demonstrated below. Please contact the Resource Data Administrator, Barry Otterson, or other Geomatics Services Section staff for connection information. Note that each person is issued an individual database user ID for accessing datasets stored in the production SDE instance (GISP on RHOracle1) and development SDE instance (GISD on RHOracle2). Click on the ellipsis button (...) to the right of the "Table List:" label to see which tables you have access to. You will note that the above table names correspond to the feature classes (layers) visible in ArcCatalog or ArcGIS. If this did not answer your question, see: • online documentation installed during your FME installation (Start | Programs | FME | Documentation) • helpfiles within FMEpedia at http://www.fmepedia.com/index.php/Main_Page.