Calling the Oracle Spatial shapefile loader from within the Oracle database itself

Oracle makes available for download from its website a shapefile loader called shp2sdo. Loading data using this tool is a four step process:

1. Execute shp2sdo to create a set of sqlplus and sqlldr files.
2. Use SQLPlus to run the SQL file generated by shp2sdo to create the table and user_sdo_geom_metadata entries.
3. Load the actual data (stored in files generated by shp2sdo) into the created table using sqlldr.
4. 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.

 package com.spatialdbadvisor.gis.oracle;
 import java.io.IOException;
 import java.sql.DriverManager;
 import java.sql.SQLException;

 public class utilities
 {
   public static int RunCommand(String command)
   {
     int exitVal = 0;
     try
     {
       Runtime rt = Runtime.getRuntime();
       Process proc = rt.exec(command);
       proc.waitFor();
       exitVal = proc.exitValue();
     } catch (Exception e)
     {
       System.out.println(e.getMessage());
       exitVal = -1;
     }
     // By convention, 0 indicates normal termination.
     return exitVal;
   }
 }

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:

 @ECHO OFF
 SET ORACLE_DB_HOME=C:\oracle\product\10.2.0\db_1
 SET CLASSPATH=%ORACLE_DB_HOME%\jdk\jre\lib
 SET JAVA_HOME=%ORACLE_DB_HOME%\jdk
 SET PATH=%JAVA_HOME%\bin;%ORACLE_DB_HOME%\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem

 %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.

 ECHO OFF
 SET ORACLE_DB_HOME=C:\oracle\product\10.2.0\db_1
 SET CLASSPATH=%ORACLE_DB_HOME%\jdk\jre\lib
 SET JAVA_HOME=%ORACLE_DB_HOME%\jdk
 SET PATH=%JAVA_HOME%\bin;%ORACLE_DB_HOME%\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem

 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:

 E:\Temp\DBUtilities\PLSQL>load_java_class
 Enter codesys username (codesys):
 Enter  password (codemgr):
 Enter TNSName (GISDB):
 arguments: '-user' 'CODESYS/CODEMGR@GISDB' '-r' '-v' '-grant' 'public' '-f' 'C:\
 temp\DBUtilities\classes\com\spatialdbadvisor\gis\oracle\utilities.cl
 ass'
 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
 Errors: 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 )
     Return Number
         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.

1. Do we execute all the steps individually or
2. 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.

 echo off
 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
 REM

 SET _directory=%1
 IF NOT EXIST %_directory% GOTO NOWINDIR
 SHIFT
 SET _ousr=%1
 SHIFT
 SET _opwd=%1
 SHIFT
 SET _osid=%1
 SHIFT
 SET _shapefile=%1
 IF NOT EXIST %_directory%\%_shapefile%.shp GOTO NOSHAPEFILE
 SET _tablename=%2
 SET _geomcolumn=%3
 SET _idcolumn=%4
 SET _start_id=%5
 SET _xmin=%6
 SET _xmax=%7
 SET _ymin=%8
 SET _ymax=%9
 shift
 SET _tolerance=%9%
 shift
 SET _srid=%9%

 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
 cd %_directory%

 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>
 REM 
 %_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 
 sqlplus %_ousr%/%_opwd%%_osid% %_directory%\%_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%

 GOTO EXITSCRIPT
 :NOSHAPEFILE
 ECHO %_directory%\%_shapefile%.shp does not exist >> %_directory%\%shapefile%.log
 GOTO EXITSCRIPT
 :NOWINDIR
 ECHO %_directory% does not exist >> %_directory%\%shapefile%.log
 :EXITSCRIPT exit
 REM =================================================================================== 

Which we would execute this way:

 declare
   v_err integer;
 begin
   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);
 end;

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.