Here’s the solution to our AUTUMN2017 puzzle, and an opportunity for you to have some fun while watching the leaves fall. All you need to solve our little puzzle is an Oracle database that supports Oracle Spatial—for example, Oracle 12c—or even Oracle 11 XE will do. You’ll also need KeepTool’s Hora. If by chance you haven’t yet installed it, just go to our download page, where you can obtain and install the free trial version with a minimum of effort.
Assuming that you’re an experienced Oracle user, you probably have an Oracle Client installed on your computer. Normally you would select a SQL*Net connection from Hora’s Connect dialog. However, for the purposes of this puzzle, imagine that you have no Oracle Client installed. No problem! Just enter the server data in the format Hostname:Port/Service. You’ll be connected to your Oracle database via TCP/IP and still be able to take advantage of almost all of Hora’s functionality. Use this method to connect to a test schema.
Your objective is to solve the puzzle with as few clicks as possible, using Oracle Spatial. We will use it in an extremely basic manner. A thorough description of the process can be found in the Oracle documentation or even in this newsletter—but you won’t need it in order to solve the puzzle.
Go to Hora’s SQL page and create the table AUTUMN2017 in your test schema by copying the following statement into the text area
CREATE TABLE AUTUMN2017 ( ID NUMBER NOT NULL, GEOMETRY MDSYS.SDO_GEOMETRY ) /
and executing it by clicking the double green arrow on the toolbar. Then likewise insert the following statement into the text area:
INSERT INTO AUTUMN2017( ID, GEOMETRY) VALUES( 1, MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(13.377704, 52.516275, Null), Null, Null)) /
Click within the text and execute it by again clicking the double green arrow. You have now created a new table called AUTUMN2017 with two columns, ID and GEOMETRY, and inserted one row into the table. (Press the Commit button on the toolbar to save your work!)
You can find out more about the SDO_GEOMETRY type in the Oracle documentation. For our purposes, only the first two numeric elements of the 3-part parameter (13.377704, 52.516275, null) are significant. These designate the latitude and longitude of a specific location.
Now we are going to take a look at the record. Open Hora’s Tables / Views page and click the Data tab. Keep an eye on the value in the GEOMETRY column.
Click first on the value in this column and then on the button with the three dots. Then click Show Geometry and you’ve solved the puzzle. You see the following picture:
It shows a portion of a map of Berlin with a pointer to the Brandenburg Gate.
The Brandenburg Gate is the iconic symbol of Berlin, and Berlin is where KeepTool was founded over 20 years ago. For all this time we have been developing tools for Oracle databases—always basing their evolution on the latest updates from Oracle. Our leading tool is Hora, which you have just now gotten to know.
Hora makes looking at data extremely easy. You can visualize the relationships between tables that are set by foreign key definitions, as well as see master/detail relationships between records. You can filter and sort records and aggregate data. Moreover, any type of administrative task is a snap with Hora. You can also develop any kind of PL/SQL code. Your download also provides additional tools that interface seamlessly with Hora.