Import Shapefile into Oracle 10g

Step 1
Download tool Oracle Shapefile Converter at http://www.oracle.com/technology/software/products/spatial/index.html and extract it into any directory.
Open the directory correspond with your operating system (ex: directory shp2sdo_nt for MS Windows), called CONVERT_HOME.
Call the directory that contains your shapefiles is SHAPE_HOME.
Call the directory that installs Oracle 10g is ORACLE_HOME.

Step 2
1. Open console, run the following commands:
> cd $SHAPE_HOME
> set path="$CONVERT_HOME"

2. Run shp2sdo converter:

• on Windows NT

shp2sdo.exe <file_name> <table_name> -g <geom_name> -d -x (<minx>,<maxx>) -y (<miny>,<maxy>) -s <SRS> -t <tolerance> -v

• on Sun Sparc Solaris or Linux

shp2sdo.exe <file_name> <table_name> -g <geom_name> -d -x \(<minx>,<maxx>\) -y \(<miny>,<maxy>\) -s <SRS> -t <tolerance> -v

Ex:
shp2sdo.exe tuyendg spa_tuyendg -g geom -d -x (575977.125,577368.125) -y (1195219.125,1196522.0) -s 32648 -t 0.5 -v

Note: Type shp2sdo.exe -h for help.


Step 3
After running shp2sdo converter, it creates 2 files:

• *.sql : to create oracle table.

• *.ctrl : to store data.

1. Open iSQL*Plus with database that you want to import, execute the content in *.sql (ex: @spa_tuyendg.sql).

2. Open file *.ctrl, insert line " CHARACTERSET UTF8" after line "LOAD DATA" and save it.

3. Open console, use Oracle SQL*Loader by running the following commands:
> set path="$ORACLE_HOME/product/10.1.0/db_1/BIN"
> sqlldr <username>/<password> <table_name>
Ex: sqlldr SYSTEM/oracle spa_tuyendg


Step 4
Open iSQL*Plus, execute the following commands:
CREATE INDEX <index_name> ON <table_name>(<geom_name>) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXECUTE SDO_MIGRATE.TO_CURRENT('<table_name>','<geom_name>');

Ex:
CREATE INDEX spa_tuyendg_spatial_idx ON spa_tuyendg(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXECUTE SDO_MIGRATE.TO_CURRENT('SPA_TUYENDG','GEOM');


View details in using_shp2sdo.txt of Oracle Shapefile Converter.