Saturday 15 December 2007

Can not export data in Oracle 10g

Error: "RemoteOperationException: ERROR: wrong password for user".
Resolve:

  1. Go to "Control Panel" -> "Admin Tools" -> "Local Security Policy."
  2. Within "Local Policies", go to user "Right Assignment."
  3. Add the user to "Logon as a Batch Job."
http://weblogs.asp.net/wallym/archive/2005/01/03/345818.aspx

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.