Calling the Oracle Spatial shapefile loader from within the Oracle database itself
Thursday February 19 2009 at 06:07
Oracle makes available for download from its website a shapefile loader called shp2sdo. Loading data using this tool is a four step process:
- Execute shp2sdo to create a set of sqlplus and sqlldr files.
- Use SQLPlus to run the SQL file generated by shp2sdo to create the table and user_sdo_geom_metadata entries.
- Load the actual data (stored in files generated by shp2sdo) into the created table using sqlldr.
- Finally, run SDO_GEOM.VALIDATE_GEOMETRY to identify incorrect geometries, correct them using SDO_UTIL.RECTIFY_GEOMETRY and other tools, processing and, finally, create spatial indexes etc.
The shp2sdo tool is an operating system tool and so runs outside of the Oracle database processes. To effect a load of a shapefile from within the database one needs a method of being able to execute the process. There are a number of ways to do this but this blog article will concentrate on one: the use of the Java Virtual Machine that ships with every Oracle database.
Firstly, we need to write a Java class that will allow us to execute an external program. There are many ways to do this as any Googling of the relevant terms shows. I did this and chose a simple approach that does not handle stdin, stdout or stderr (and so is limited as to the error notification that can be generated).
Here is my class.
public class utilities
public static int RunCommand(String command)
int exitVal = 0;
Runtime rt = Runtime.getRuntime();
Process proc = rt.exec(command);
exitVal = proc.exitValue();
} catch (Exception e)
exitVal = -1;
// By convention, 0 indicates normal termination.
Note that the function expects a single command string so if you want to pass parameters to an executable you have to put them all together with their command. See later for example.
We compile this using the java compiler that is shipped with the Oracle database (as the versions have to be exactly the same).
Since I did this work on Windows I created a cmd file which contained the following:
%JAVA_HOME%\bin\javac -classpath .;%ORACLE_DB_HOME%\lib\xmlparserv2.jar;%ORACLE_DB_HOME%\jdbc\lib\ojdbc14.jar;%ORACLE_DB_HOME%\md\lib\sdoutl.jar;%ORACLE_DB_HOME%\md\lib\sdoapi.jar -d C:\Temp\DBUtilities\classes C:\temp\DBUtilities\src\com\spatialdbadvisor\gis\oracle\utilities.java
Once this is compiled, we then use the Oracle loadjava tool to load the java class we have just compiled into Oracle. Again, I wrapped this inside a WIndows cmd tool.
IF %ORACLE_SID%_ EQU _ SET ORACLE_SID=GISDB
SET /P ousr=Enter codesys username (codesys):
IF %ousr%_ EQU _ SET ousr=CODESYS
SET /P opwd=Enter %ouser% password (codemgr):
IF %opwd%_ EQU _ SET opwd=CODEMGR
SET /P osid=Enter TNSName (%ORACLE_SID%):
IF %osid%_ EQU _ SET osid=%ORACLE_SID%
loadjava -user %ousr%/%opwd%%osid% -r -v -grant public -f C:\Temp\DBUtilities\classes\com\spatialdbadvisor\gis\oracle\utilities.class
Which, if successful, should give messages like this:
Enter codesys username (codesys):
Enter password (codemgr):
Enter TNSName (GISDB):
arguments: '-user' 'CODESYS/CODEMGR@GISDB' '-r' '-v' '-grant' 'public' '-f' 'C:\
creating : class com/spatialdbadvisor/gis/oracle/utilities
loading : class com/spatialdbadvisor/gis/oracle/utilities
granting : execute on class com/spatialdbadvisor/gis/oracle/utilities to public
resolving: class com/spatialdbadvisor/gis/oracle/utilities
Classes Loaded: 1
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
So far, so good.
We now have a compiled Java class in the Oracle JVM,
But how do we execute it?
Firstly we have to create a PL/SQL function wrapper over the top of it as follows.
create or replace Function RunCommand( p_command in varchar2 )
As language java name
'com.spatialdbadvisor.gis.oracle.utilities.RunCommand(java.lang.String) return oracle.sql.string';
Now, we have the ability to execute an external program.
Or do we?
The Oracle Database is a very secure application. No organisation or the DBA it entrusts to ensure data security, wants to jeopordise database secutiry. Oracle’s JVM’s security is managed from the database. We can’t just have our new tool execute any program reading and writing data anywhere on disk. It has to be controlled.
Oracle does this via DBMS_JAVA’s GRANT_PERMISSION procedure. This procedure allows the DBA to grant read, write, execute, delete permissions directly to a user or via a role.
So, we need to use this to grant appropriate permissions.
For this article I copied the Oracle shp2sdo.exe application and the GeoScience Australia GeoData250K shapefiles (GeoData250K_Tas_roads.*) to the C:\Temp directory. I will do all my processing in this directory.
So, first, we need to grant appropriate permissions. As the “system” user I executed:
execute dbms_java.grant_permission( 'CODESYS', 'SYS:java.io.FilePermission', 'c:\Temp\*', 'read,write,execute,delete' );
Now we are ready to load our data.
Remember that our load is a multi-step process in which three of the processes have to be executed externally and one internally. So we now have some choices.
- Do we execute all the steps individually or
- Do we wrap all the external processes up into a single process and execute it followed by the post-load internal processes?
Because there are a number of issues relating to flow of control, operating system environment settings etc, I decided to use step two and put all three processing steps into a single command shell (or Linux/Unix shell).
Now, Windows shell programming is inflexible (Linux shell programming is far better), so please do not get too critical of what follows! To control the external processes I created a Windows command tool called shp2sdo.cmd. Here is what it does.
REM Script Usage: <user> <password> <sid> <shapefile> <tablename> <geometry column> <id column> <start_id> <xmin> <xmax> <ymin> <ymax> <srid>
rem shp2sdo.cmd GeoData250K_Tas_roads GeoData250K_Tas_roads geom gid 1 -180 180 -90 90 0.05 8311
IF NOT EXIST %_directory% GOTO NOWINDIR
IF NOT EXIST %_directory%\%_shapefile%.shp GOTO NOSHAPEFILE
REM Note that for some strange reason Oracle will execute this batch file from: %ORACLE_HOME%\DATABASE
REM eg c:\oracle\product\10.2.0\db_1\DATABASE so we must ensure that the data And the Shp2Sdo
REM executable all exist within the directory we have read/write/execute permissions from within Oracle.
REM This is NOT cross-platform as it is only an example for publication
SET > %_directory%\%shapefile%shapefile%log
echo shp2sdo -o %_shapefile% %_tablename% -g %_geomcolumn% -i %_idcolumn% -n %_start_id% -d -x (%_xmin%,%_xmax%) -y (%_ymin%,%_ymax%) -t %_tolerance% -s %_srid% >> %_directory%\%shapefile%.log
REM USAGE: shp2sdo [-o] <shapefile> <tablename> -g <geometry column>
REM -i <id column> -n <start_id> -p -d
REM -x (xmin,xmax) -y (ymin,ymax) -s <srid>
%_directory%\shp2sdo -o %_directory%\%_Shapefile% %_tablename% -g %_geomcolumn% -i %_idcolumn% -n %_start_id% -d -x (%_xmin%,%_xmax%) -y (%_ymin%,%_ymax%) -t %_tolerance% -s %_srid%
REM Create the table
echo sqlplus %_ousr%/%_opwd%%_osid%
%_tablename%.sql >> %shapefile%.log
Rem The Oracle tool does not put an EXIT or QUIT at the end of %_tablename%.sql so add it.
echo EXIT; >> %_tablename%.sql
echo sqlldr %_ousr%/%_opwd%%_osid% %_directory%\%_tablename% >> %shapefile%.log
REM Finally, load the data using sql*loader.
sqlldr %_ousr%/%_opwd%@%_osid% %_directory%\%_tablename%
ECHO %_directory%\%_shapefile%.shp does not exist >> %_directory%\%shapefile%.log
ECHO %_directory% does not exist >> %_directory%\%shapefile%.log
Which we would execute this way:
v_err := RunCommand('c:\temp\shp2sdo.cmd c:\temp gis gis GISDB GeoData250K_Tas_roads GeoData250K_Tas_roads geom gid 1 -180 180 -90 90 0.05 8311' );
dbms_output.put_line('RunCommand (0==SUCCESS) = ' || v_err);
RunCommand (0==SUCCESS) = 0
After executing RunCommand, we can check the results in SQL Developer.
And then go on to create spatial indexes etc. (This could be added to the above processing: I leave that as an exercise for the interested used.)
This is pretty neat and simple processing (I do not present myself as a seasoned or experienced Java programmer). However, note that most DBAs get very worried about this sort of processing so, if you are thinking about implementing this sort of processing in your database, make sure you provide your DBA with lots of chocolates and beer.
I hope this article is useful to someone.