Exporting to Oracle

SIS Desktop can export data to a number of databases including Oracle.

Only 32-bit SIS Desktop 9.1 and GeognoSIS support the use of Oracle with the following database dataset types:

  • Cadcorp Editable Dataset
  • Cadcorp View Geometry
  • Cadcorp View Points
  • OGC SQL92 Dataset

64 -bit SIS Desktop 9.1 does not support these database dataset types in Oracle.

Loading data into Oracle

Cadcorp SIS Desktop 9.1 uses the SDO_Geometry type to store the spatial data.

Spatial data contained in any Cadcorp SIS Desktop 9.1 overlay can be exported to Oracle. All geometry features as well as any attributes contained in the overlay schema are exported. The export utility can also create spatial indexes.

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

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

Exporting to Oracle

Cadcorp SIS Desktop 9.1 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 Desktop 9.1

    Corresponding column definition in Oracle

    Text

    VARCHAR2 (4000)

    Float

    NUMBER (20,6)

    Integer

    NUMBER(10)

    Geometry

    MDSYS.SDO_GEOMETRY

    Date

    DATE


    5. 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 Desktop 9.1 can be adjusted by the database administrator. For example columns can be resized according to the Oracle rules.

Cadcorp SIS Desktop 9.1 and Table Definition changes

Structural changes made from outside of Cadcorp SIS Desktop 9.1 are only reflected in Cadcorp SIS Desktop 9.1 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.

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

  • The Overlay contains attributes created in Cadcorp SIS Desktop 9.1 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: Cadcorp SIS Desktop 9.1 only supports the default naming convention. Cadcorp SIS Desktop 9.1 does not support tables created using non default conventions. For example Cadcorp SIS Desktop 9.1 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 Cadcorp SIS Desktop 9.1 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 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 Cadcorp SIS Desktop 9.1 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 Cadcorp SIS Desktop 9.1 from being able to style (colour) the data to match the original data. Cadcorp SIS Desktop 9.1 will display all the data in a single default colour.