Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
CREATE TABLE EW_ABGANG_SCH
(
FID NUMBER(10) NOT NULL,
GID NUMBER(10),
DELETED NUMBER(1) DEFAULT 0 NOT NULL,
TB_POINT_NUMBER NUMBER(15),
FID_SCHEMA NUMBER(10),
FID_LEITUNGSABS_SCH NUMBER(10),
GEOM MDSYS.SDO_GEOMETRY
)
COLUMN GEOM NOT SUBSTITUTABLE AT ALL LEVELS
VARRAY "GEOM"."SDO_ELEM_INFO" STORE AS LOB (
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
CACHE
LOGGING
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
VARRAY "GEOM"."SDO_ORDINATES" STORE AS LOB (
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
CACHE
LOGGING
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE USERS
LOGGING
NOCOMPRESS
NOCACHE;
Debug output:
[2024-01-11 13:55:10] DEBUG: Formatting bulk of 2000 data (real: 514 rows) for PostgreSQL.
[2024-01-11 13:55:10] DEBUG: Creating output for 2000 tuples
[2024-01-11 13:55:10] DEBUG: Sending INSERT output directly to PostgreSQL backend
[2024-01-11 13:55:10] FATAL: FEHLER: Syntaxfehler bei ╗)½
LINE 2: ...noten,fid_schema,geom) VALUES (ST_Geometry('247', ),ST_Geome...
^
This is the spatial section of ora2pg.conf:
#------------------------------------------------------------------------------
# SPATIAL SECTION (Control spatial geometry export)
#------------------------------------------------------------------------------
# Enable this directive if you want Ora2Pg to detect the real spatial type and
# dimensions used in a spatial column. By default Ora2Pg will look at spatial
# indexes to see if the layer_gtype and sdo_indx_dims constraint parameters have
# been set, otherwise column will be created with the non-constrained "geometry"
# type. Enabling this feature will force Ora2Pg to scan a sample of 50000 lines
# to look at the GTYPE used. You can increase or reduce the sample by setting
# the value of AUTODETECT_SPATIAL_TYPE to the desired number of line.
AUTODETECT_SPATIAL_TYPE 1
# Disable this directive if you don't want to automatically convert SRID to
# EPSG using the sdo_cs.map_oracle_srid_to_epsg() function. Default: enabled
# If the SDO_SRID returned by Oracle is NULL, it will be replaced by the
# default value 8307 converted to its EPSG value: 4326 (see DEFAULT_SRID)
# If the value is upper than 1, all SRID will be forced to this value, in
# this case DEFAULT_SRID will not be used when Oracle returns a null value
# and the value will be forced to CONVERT_SRID.
# Note that it is also possible to set the EPSG value on Oracle side when
# sdo_cs.map_oracle_srid_to_epsg() return NULL if your want to force the value:
# Ex: system> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;
CONVERT_SRID 1
# Use this directive to override the default EPSG SRID to used: 4326.
# Can be overwritten by CONVERT_SRID, see above.
DEFAULT_SRID 25832
# This directive can take three values: WKT (default), WKB and INTERNAL.
# When it is set to WKT, Ora2Pg will use SDO_UTIL.TO_WKTGEOMETRY() to
# extract the geometry data. When it is set to WKB, Ora2Pg will use the
# binary output using SDO_UTIL.TO_WKBGEOMETRY(). If those two extract type
# are called at Oracle side, they are slow and you can easily reach Out Of
# Memory when you have lot of rows. Also WKB is not able to export 3D geometry
# and some geometries like CURVEPOLYGON. In this case you may use the INTERNAL
# extraction type. It will use a pure Perl library to convert the SDO_GEOMETRY
# data into a WKT representation, the translation is done on Ora2Pg side.
# This is a work in progress, please validate your exported data geometries
# before use.
GEOMETRY_EXTRACT_TYPE INTERNAL
# Oracle function to use to extract the srid from ST_Geometry meta information
ST_SRID_FUNCTION ST_SRID
# Oracle function to use to extract the dimension from ST_Geometry meta information
ST_DIMENSION_FUNCTION ST_DIMENSION
# Oracle function to used to convert an ST_Geometry value into WKB format
ST_ASBINARY_FUNCTION ST_ASBINARY
# Oracle function to used to convert an ST_Geometry value into WKT format
ST_ASTEXT_FUNCTION ST_ASTEXT
# Oracle function to use to extract the geometry type from a ST_Geometry column
ST_GEOMETRYTYPE_FUNCTION ST_GEOMETRYTYPE
ora2pg version:
Commandline:
ora2pg -t INSERT -o data-INSERT.sql -b BSA_Topobase_STROM\data -c BSA_Topobase_STROM\config\ora2pg-TOPOBASE_STROM.conf --debug
Table DDL:
Debug output:
This is the spatial section of ora2pg.conf: