<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-family: Calibri, sans-serif;">
<div>Aileen,</div>
<div><br>
</div>
<div>With the TRUNCATE option, do not use –append since append will disable the use of layer creation options. </div>
<div><br>
</div>
<div>Use ogr2ogr -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI::xxxxx/xxxx@XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy!" open_country -nln open_country3 -lco TRUNCATE=yes</div>
<div><br>
</div>
<div>Mike</div>
<div><br>
</div>
<div>
<div>
<div>
<div>-- </div>
<div>Michael Smith</div>
</div>
<div>Remote Sensing/GIS Center</div>
<div>US Army Corps of Engineers</div>
</div>
</div>
<div><br>
</div>
<div><br>
</div>
<span id="OLK_SRC_BODY_SECTION">
<div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1pt solid; BORDER-RIGHT: medium none; PADDING-TOP: 3pt">
<span style="font-weight:bold">From: </span>Aileen Heal <<a href="mailto:aileenheal@astuntechnology.com">aileenheal@astuntechnology.com</a>><br>
<span style="font-weight:bold">Date: </span>Friday, June 20, 2014 at 7:51 AM<br>
<span style="font-weight:bold">To: </span>"<a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>" <<a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>><br>
<span style="font-weight:bold">Subject: </span>[EXTERNAL] [gdal-dev] How to use TRUNCATE lco with Oracle Spatial driver<br>
<span style="font-weight:bold">Resent-From: </span>Michael Smith <<a href="mailto:michael.smith@usace.army.mil">michael.smith@usace.army.mil</a>><br>
</div>
<div><br>
</div>
<div>
<div>
<div dir="ltr">
<p style="margin-bottom:0cm">Can any one provide an example of how to use the TRUNCATE lco for the Oracle Spatial driver.</p>
<p style="margin-bottom:0cm">According to the docs. </p>
<p style="margin-bottom:0cm">TRUNCATE: This may be "YES" to force the existing table to be reused, but to first truncate all records in the table, preserving indexes or dependencies.</p>
<p style="margin-bottom:0cm">Which is exactly what I want, but every time I try to use it I get warnings or errors.</p>
<p style="margin-bottom:0cm">e.g. First create a simple table.<br>
<br>
ogr2ogr -overwrite -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI:xxxxx/xxxx@XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy" open_country -lco LAUNDER=yes -lco GEOMETRY_NAME=GEOLOC -nln open_country3</p>
<p style="margin-bottom:0cm">so I tried using append with TRUNCATE lco.</p>
<p style="margin-bottom:0cm">ogr2ogr -append -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI::xxxxx/xxxx@XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy!" open_country -nln open_country3 -lco TRUNCATE=yes</p>
<p style="margin-bottom:0cm">which resulted in the the following<br>
WARNING: Layer creation options ignored since an existing layer is being appended to.</p>
<p style="margin-bottom:0cm">The data is appended and the table is not truncated.</p>
<p style="margin-bottom:0cm">So I tried this:</p>
<p style="margin-bottom:0cm">ogr2ogr -overwrite -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI:xxxxx/xxxx@XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy" open_country -lco LAUNDER=yes -lco GEOMETRY_NAME=GEOLOC -lco TRUNCATE=yes
-nln open_country3</p>
<p style="margin-bottom:0cm">and I get the following errors</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in TRUNCATE TABLE "OPEN_COUNTRY3"</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-04043: object OPEN_COUNTRY3 does not exist in OCIDescribeAny</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-04043: object "OPEN_COUNTRY3" does not exist in OCIDescribeAny</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-04043: object "OPEN_COUNTRY3" does not exist in OCIDescribeAny</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-04043: object "OPEN_COUNTRY3" does not exist in OCIDescribeAny</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "DISPLAYNAM" VARCHAR2(150)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "POLICY_REF" VARCHAR2(100)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "LAYER_NO" NUMBER(10)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "AREA_HA" NUMBER(24,15)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "TEXT_REF" VARCHAR2(20)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "LAYER_NAME" VARCHAR2(50)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "SETTLEMENT" VARCHAR2(40)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "NO_UNITS" NUMBER(10)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "MI_STYLE" VARCHAR2(254)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "MI_PRINX" NUMBER(10)</p>
<p style="margin-bottom:0cm">ERROR 1: ORA-00942: table or view does not exist in ALTER TABLE OPEN_COUNTRY3 ADD "DISPLAYNAME" VARCHAR2(150)</p>
<p style="margin-bottom:0cm">So it appears the -overwrite is dropping the table and then trying to truncate it hence the errors.</p>
<p style="margin-bottom:0cm">So I try this</p>
<p style="margin-bottom:0cm">ogr2ogr -s_srs "EPSG:27700" -t_srs "EPSG:27700" -f OCI OCI::xxxxx/xxxx@XE PG:"dbname=postgis active_schema=planning host=localhost user=yyyy password=yyyy" open_country -nln open_country3 -lco TRUNCATE=yes</p>
<p style="margin-bottom:0cm">And I get the following.</p>
<p style="margin-bottom:0cm">FAILED: Layer open_country3 already exists, and -append not specified.</p>
<p style="margin-bottom:0cm">Consider using -append, or -overwrite.</p>
<p style="margin-bottom:0cm">ERROR 1: Terminating translation prematurely after failed</p>
<p style="margin-bottom:0cm">translation of layer open_country (use -skipfailures to skip errors)</p>
<p style="margin-bottom:0cm">Not sure what to try next.</p>
<p style="margin-bottom:0cm">TIA</p>
<p style="margin-bottom:0cm"><br>
</p>
</div>
<br>
<a href="https://astuntechnology.com/ishare/2014-enterprise-gis-roadshows/" target="_blank"><img src="https://astuntechnology.com/media/website/images/library/supporting_badge_small.png"></a></div>
</div>
</span>
</body>
</html>