Oracle Database 18c,
often simply called Oracle for
short, is one of the three most popular enterprise-class DBMS packages.
This topic is the final step in a four topic sequence showing a complete
installation and then utilization of Oracle 18c XE from the very beginning.
Please see the Install
Oracle topic for basic info on Oracle XE.
Disclaimer:
These topics were accurate when written, using the versions of installation
software indicated. Third party packages can and do change, so this
topic may be out-of-date. It is provided as an example of how such
installations can be approached.
Installing Oracle XE and readying it for use in typical installations involves four steps:
Open a Firewall Port for Oracle
Connect to Oracle
This is the final topic in the above sequence of steps.
In this topic, we are using a 64-bit Windows 10 system on which we have installed the Oracle Instant Client software. This entire topic was conducted using Manifold on a remote machine, connecting to the Oracle server through the network.
So far, we have installed Oracle XE on our target machine. We have created and opened read/write a pluggable database within Oracle XE called gispdb. We have created an admin user gis_adm with full authority to work with the gispdb pluggable database. Now, we will use that database to show an example of copying and pasting data from Manifold into Oracle XE.
Every machine running Manifold which
will be used to connect to Oracle must
have Oracle Instant Client software installed. If we want
to connect to our Oracle XE server from a remote machine running Manifold,
we must install Oracle Instant
Client software on that remote machine. That is easy to do, as was
seen in the Configure
Oracle topic.
If we have teleported into this topic without reading the preceding topics in this case study, we should make sure to install client software as illustrated in the Configure Oracle topic.
We continue from the end of the Configure Oracle topic. We right-click into the Project pane to create a new data source.
Right-click into the Manifold part of the Project pane and choose Create - New Data Source.
In the dropdown menu we then choose More... to launch the New Data Source dialog.
Enter a descriptive name, Oracle GISPDB as gis_adm, and choose Database: oracle as the type. Click the browse [...] button to launch the Database Login dialog.
Enter the server connection string, which starts IP address of the server, which in this example is 192.168.2.44, to which after a colon : character the port 1521 is appended, followed by a forward slash / character and the name of the pluggable database, GISPDB, to which we would like to connect. Case is not significant, but many Oracle users prefer to capitalize the names of databases while using lower case for user names.
Choose Use login and password and then enter gis_adm as the login. Enter the password we provided for the gis_adm user when creating the user in SQL Plus in the Configure Oracle topic.
It is a good idea to check for errors by pressing the Test button.
If that does not report a connection, we should fix any mistakes. When we see Connection established we press OK.
Back in the Database Login dialog, press OK.
Back in the New Data Source dialog we press Create Data Source.
A new data source called Oracle GISPDB as gis_adm appears in our Project pane. We can expand it as seen above, to see the large number of tables that are part of the infrastructure of an Oracle pluggable database. That data source is a connection to the pluggable database called gispdb within our Oracle XE server, a connection that has been made by the gis_adm user with all the privileges of that user for any operations within that data source.
We now have two data sources within our Manifold project which both connect to the same pluggable database. One of the data sources is a connection with the full privileges of the c##manifold user, while the other is a connection with the DBA privileges of the gis_adm user. That is why we named the data sources to include the name of the user, so we can keep straight the two different sets of privileges.
We can load the Oracle XE DBMS with components from Manifold projects by simply copying and pasting them into the DBMS data source, or even more simply, using drag and drop. We can also copy data from the DBMS by simply copying from the DBMS data source and pasting into the Manifold project.
First, we clean up our display a bit. We no longer need the Oracle GISPDB as c##manifold data source, so we will click that data source to highlight it and then press Delete to remove it from the project. This simply removes it from our project and does not delete anything in the Oracle XE installation.
Next, we open another Manifold session (not illustrated) in which we have stored some drawings of Mexico using different projections. We copy and paste those into our working session, as seen above. We have pasted two drawings, each with its table. One drawing shows Mexico using Latitude / Longitude projection, and the other shows Mexico using Pseudo-Mercator projection.
In the illustration above, we have opened the Latitude / Longitude drawing, named MexicoLL.
To copy that drawing and its table into our gispdb database, we ctrl-click on MexicoLL and MexicoLL Table, to highlight them. We then press the Copy button on the Project pane's toolbar (or, we can press Ctrl-C to Copy).
Next, we click on the Oracle GISPDB as gis_adm data source to highlight it. We then press the Paste button on the Project pane's toolbar (or, we can press Ctrl-V to Paste).
Oracle chugs away, loading the data we have pasted into the gispdb pluggable database.
The new components appear in the Oracle XE gispbd pluggable database, with their names automatically converted into Oracle names, and some new, virtual components, such as drawings, created.
To provide a consistent, orderly, user interface, Manifold will automatically generate virtual components, such as folders, some Manifold system tables, and some automatically-generated drawings, that appear to be in the database but which are generated on the fly from metadata and are not a new, real table or other new item added to the database. See the Real and Virtual Components topic for discussion and examples.
In the illustration above, we have double-clicked the GIS_ADM.MexicoLL drawing to open it. It opens just as if it was the original drawing within the Manifold .map project, still retaining the Style with which it was formatted within Manifold. Copying drawings and their tables into Oracle saves the style those drawings use as well.
If we do not want the visual clutter of many Oracle system components, we can enter GIS_ADM into the filter box of the Project pane to show only those components, the ones we pasted into the database from our Manifold project. When converting Manifold component names into Oracle names, the name of the user is used as a prefix. If all of our user names are named to begin with gis_, we can just enter GIS_ into the filter box to see only those components added by our GIS users from Manifold.
We can switch to the Component tab of the Info pane to get information on the drawing we have opened. Manifold has automatically specified the coordinate system within Oracle as required by Oracle.
We can copy another drawing into our Oracle database if we like. In the illustration above, we have contracted the Oracle GISPDB as gis_adm data source and we have cleared the Project pane's filter box so that all components are shown. We have closed the GIS_ADM.MexicoLL drawing to simplify the illustration.
We ctrl-click on MexicoPM and MexicoPM Table to highlight them. We then press the Copy button on the Project pane's toolbar (or, we can press Ctrl-C to Copy).
Next, we click on the Oracle GISPDB as gis_adm data source to highlight it. We then press the Paste button on the Project pane's toolbar (or, we can press Ctrl-V to Paste).
In the illustration above we have entered
GIS into the Project Pane's filter
box to show only the components we have just pasted into the gispdb pluggable
database.
We double-click on the GIS_ADM.MexicoPM drawing within the Oracle GISPDB as gis_adm data source to open that drawing. It opens, also still retaining the Style with which it was formatted within Manifold.
The Component tab of the Info pane shows information on the GIS_ADM.MexicoPM drawing. Manifold has automatically specified the coordinate system within Oracle as required by Oracle.
However, instead of using the name Geom_x for the spatial index as used by the GIS_ADM.MexicoLL drawing, the GIS_ADM.MexicoPM drawing uses a very long name for the index. That is because Oracle requires the name of an index to be unique throughout an entire database, and not just unique within a particular table. Manifold therefore synthesizes on the fly a unique name for indexes as needed within Oracle.
If we compare the above illustration to the analogous illustrations for the Info pane of the gisdb.MexicoPM in the Connect to MySQL topic, and the dbo.MexicoPM drawing in the Connect to SQL Server topic, we see a difference between how MySQL and SQL Server name indexes and how Oracle names indexes. SQL Server and My SQL require that an index name be unique within a table, but they both allow the same index name to be used in different tables. If we have an index called Geom_x in one table we are free to use that same name for an index, Geom_x in different tables. That is the same as in Manifold, so there is no need to rename an index when copying multiple tables to MySQL or to SQL Server that use the same name for an index. Our drawing as seen above therefore uses the same Geom_x name for the spatial index that most Manifold drawings use by default.
Oracle and PostgreSQL are different in that Oracle and PostgreSQL require index names to be unique throughout an entire database. If one table in the database uses Geom_x as an index name, then no other table in the database can use Geom_x as a name for an index. When Manifold copies multiple tables to Oracle, if an index name is already used by one of those tables, Manifold will, on the fly, synthesize a new index name that does not conflict, automatically adjusting references to that index name in other components.
We can work with components, like drawings and tables, saved into the database as if they were local components stored within our project. So far, we have just opened and viewed the GIS_ADM.MexicoLL drawing and the GIS_ADM.MexicoPM drawing, but we can do more than viewing. We can work with them as if they were resident within the project.
For example, we can Alt-click the province of Durango in the GIS_ADM.MexicoPM drawing and immediately the attributes will appear in the Info pane. We can click any segment or handle in that province to enable it for editing with the Coordinates tab, just as if it were a local drawing.
Or we can click on the Coordinates tab to show the coordinates, and thus enable the object for editing.
If we Ctrl-click the province of Chihuahua, it will immediately be selected and appear in red selection color, just like it would in a local drawing.
Open the drawing's table in the database, GIS_ADM.MEXICOPMTABLE, and we see that the corresponding record for Chihuahua is also selected and shown in red selection color, just as it would be with a local drawing's table. In the illustration above, we have used the Layers pane to rearrange the order and width of columns in the table, for a more interesting view.
We have finished the last topic in the case study on installing Oracle XE.
See the preceding topic: Configure Oracle
Jump to the beginning of the case study: Install Oracle
File - Create - New Data Source
Open a Firewall Port for Oracle
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines