Exporting to Oracle

SIS is able to export data to a number of databases. This topic deals with exporting data to an Oracle database.

Loading data into Oracle

SIS provides the ability to export (load) data into an Oracle database using the SDO_Geometry type to store the spatial data. Spatial data contained in any SIS overlay can be exported to Oracle. SIS will export all the geometry features as well as any attributes contained in the overlay schema. The export utility can also create spatial indexes.

The table structure that is exported to Oracle is based on the Overlay Schema.

Cadcorp recommend that data to be exported to Oracle is first copied to an internal overlay so that the data and the schema columns can be adjusted to conform to the Oracle model.

Exporting to Oracle

When data is exported from SIS to Oracle SIS uses the following conventions when creating database tables and supporting objects..

Table Definitions

Spatial data is exported to Oracle in the following steps:

1. An Oracle table is created in the users default tablespace. All optional parameters are default values (your database administrator will know the default values for your system). For most users the default options are irrelevant.
   
   
2. Associated spatial indexes are created in the users default table space using default optional parameters.
   
   
3. Table and column names default to upper case.
   
   
4. Columns definitions default to the following Oracle column types:
   
   

   Data in SIS	Corresponding column definition in Oracle
   Text	VARCHAR2 (4000)
   Float	NUMBER (20,6)
   Integer	NUMBER(10)
   Geometry	MDSYS.SDO_GEOMETRY
   Date	DATE

   
   Note: When exporting text data to Oracle, PostGIS or SQL Server 2008 spatial databases the following expression should be added to the schema of the overlay to be exported. The expression should be renamed text_height.

_ Point_ Height * 0.000352777778 * [dataset Scale]

where _ Point_ Height is a system property

dataset Scale is the dataset scale for the overlay being exported.

Adjusting Table Definitions

The definitions of tables created by SIS can be adjusted by the database administrator. For example columns can be resized according to the Oracle rules.

SIS and Table Definition changes

Structural changes made from outside of SIS are only reflected in SIS when the SWD is re-opened or the overlay is added to a SWD. For example if a new column is added to the table it will only be available in the overlay schema if the SWD is re-opened or if the overlay is added again.

SIS will not display new table definitions in the following circumstances:

• The Overlay contains attributes created in SIS that do not exist as a part of the table definition in Oracle. For example expression columns such as calc interior.
• The overlay is using a saved schema definition.

In both these cases the overlay will have to be added again to the SWD to ensure that the new table definition is read. Any expression columns on the original overlay will need to be added to the new overlay.

Naming Convention

Oracle tables and column names must conform to Oracle naming conventions. This includes:

• names should be less than 31 characters
• names should start with an alphabetic character
• should not contain Oracle reserved words such as: "select" ;or contain spaces or certain characters

Note:SIS only supports the default naming convention. SIS does not support tables created using non default conventions. For example SIS will not open tables that have the following names or columns within the table:

Names in lower case

Names with spaces etc.

See Oracle documentation for more information.

Changing the Name of a Table

Users should not change the name of an Oracle spatial table. Changing the name of an Oracle table containing spatial data causes the following issues:

• SWD containing Oracle overlays will not be able to retrieve data from the table.
• The Oracle spatial layer will be broken causing SIS to be unable to see the spatial layer.

Simple Features Compliant

Oracle supports data that conforms to the OpenGIS Simple Features Specification.

Existing Table Overwritten

If Export to Server [File-Export] is used to export a layer and the user specifies a table name of an existing table in Oracle (whether spatial or not) the existing table in Oracle will be replaced.

Exporting Oracle Overlays

If users have a requirement to export an Oracle spatial overlay back to Oracle it is recommended that the data be read into SIS as a cached overlay before exporting it to Oracle.

Feature Tables

When exporting data to Oracle ensure that the data has been appropriately feature coded before exporting to Oracle. Failure to do so will prevent SIS from being able to style (colour) the data to match the original data. SIS will display all the data in a single default colour.

Top of page

Send comments on this topic.

Click to return to www.cadcorp.com

© Copyright 2000-2017 Computer Aided Development Corporation Limited (Cadcorp).