The process in the previous post loaded one layer at a time. This batches the process.
Some preparation of a text file with a list of all Oracle table names to be extracted and loaded into PostGIS is required.
rem This reads a list of table names from txt file and passes them to ogr2ogr which then exports from Oracle into PostGIS
for /F %%f in (C:\path\to\your\textfile\OracleTables.txt) do ogr2ogr -a_srs EPSG:27700 -overwrite -f "PostgreSQL" -nln yourschema.%%f PG:"host=localhost user=youruser password=yourpassword dbname=yourdatabase" OCI:"oracleuser/oraclepassword@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521)))(CONNECT_DATA = (SID =oraclesid))):oracleschema.%%f"
Traditionally exporting from Oracle to PostGIS involved an intermediate step Oracle to Shapefile and then Shapefile to PostGIS. But this means that field names longer than 10 characters in Oracle get truncated in the conversion to Shapefile. And using the QGIS or PgAdminIII shp2pgsql gui has no easy way to set the schema or SRID other than manual intervention. I guess it could be run from the command line though…
Using Safe’s FME you can go directly from Oracle to PostGIS. But there is no way to define the destination schema in the PostGIS database. The PostGIS writer expects there to be a schema with the same name as the Oracle schema in order to complete the extraction and translation. See https://groups.google.com/forum/#!topic/fmetalk/zQsUUJNR5h8
So, open-source here we come. ogr2ogr can read and write to a number of different formats including Oracle (this uses the proprietary OCI.dll). On Windows install OSGeo4W with GDAL/OGR. Run the setup.exe and “Advanced Install” to install the gdal-oracle10g package. See http://stackoverflow.com/questions/9789686/migrate-spatial-data-from-oracle-to-postgresql
The command to issue at the command line is (all on one line):
ogr2ogr -a_srs <srs> -overwrite -f "PostgreSQL" -nln <postgis schema>.<postgis table> PG:"host=<postgis host> user=<postgis user> password=<postgis password> dbname=<postgis database>" OCI:"<oracle user>/<oracle password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port number>)))(CONNECT_DATA = (SID =<sid name>))):<oracle schema>.<oracle table>"
<srs> = in the EPSG format e.g EPSG:27700
This uses ogr2ogr to connect to an Oracle database, pull a table from a schema and write it to a defined schema in PostGIS database.