<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.Shkpostityyli20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Good that it works for you now. Myself I do not get an error from these either:<br>
<br>
ogr2ogr -append OCI:system/password:TEST test.shp<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">ogr2ogr -append OCI:system/password:TEST test.shp -nln test<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">The Oracle default to use uppercase names affects the connection string but I could not manage to get your original error by playing with upper/lower case in various places of the command.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">-Jukka-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> Kreß, Marius <KressM@eba.bund.de> <br>
<b>Lähetetty:</b> perjantai 13. marraskuuta 2020 19.58<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML) <jukka.rahkonen@maanmittauslaitos.fi><br>
<b>Kopio:</b> 'gdal-dev@lists.osgeo.org' <gdal-dev@lists.osgeo.org><br>
<b>Aihe:</b> AW: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Hi,<br>
<br>
As it often is with this things, it was actually pretty simple: I included the table name in the connection. It seems that this confused the program. But when I did it like you, with just username, password and server, it worked.
<br>
<br>
Thanks for your help,<br>
Marius Kress <o:p></o:p></p>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="x_divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">Von:</span></b><span style="color:black"> Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Gesendet:</b> Freitag, 13. November 2020 08:48:03<br>
<b>An:</b> Kreß, Marius<br>
<b>Cc:</b> 'gdal-dev@lists.osgeo.org'<br>
<b>Betreff:</b> Re: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?</span>
<o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt">Hi,<br>
<br>
It have not been using Oracle lately but yesterday I installed the free XE version with default values on Windows. Then I made the following simple test (I apologize all the DB admins I have been working with).<br>
<br>
ogr2ogr OCI:system/password test.shp<br>
ogr2ogr -update -append OCI:system/password test.shp -nln test<br>
<br>
Ogr2ogr is adding new features into the table without errors. I also tried with "-nln test_test" and it worked as well.<br>
<br>
If I run the command again without the -append option the error I get is <br>
ERROR 1: Layer test already exists, and -append not specified. Consider using -append, or -overwrite.<br>
<br>
Quite different message. Because you get an Oracle error ERROR 1: ORA-00955 I guess that it means that<br>
1) GDAL does not know that the table already exists and<br>
2) therefore it tries to create a new table.<br>
<br>
It may be that your Oracle user does not have all required rights. Does ogrinfo list the table isophone_test? Did you read the few errors that you got when you fired the first ogr2ogr command (sometimes they are meaningful)? Does your user have rights to insert
and update also the spatial metadata tables in Oracle? Run the -append command again by adding "--debug on" into the command and you will see what SQL statements GDAL creates. Here is what one of my tests produced. You can test the SQL commands with SQLPlus
and it may tell you what rights are missing.<br>
<br>
OCI: From 'Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production' :<br>
OCI: Version:18<br>
OCI: Release:0<br>
OCI: Prepare(ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD' NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM' NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM'
NLS_NUMERIC_CHARACTERS = '. ')<br>
OCI: Prepare(SELECT TABLE_NAME, OWNER FROM ALL_SDO_GEOM_METADATA)<br>
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT<br>
FROM user_tab_columns<br>
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))<br>
OCI: Prepare(SELECT COUNT(*)<br>
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t<br>
WHERE u.table_name = :table_name<br>
AND u.column_name = :geometry_name)<br>
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))<br>
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER(:table_name) AND COLUMN_NAME = UPPER(:geometry_name))<br>
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT<br>
FROM user_tab_columns<br>
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))<br>
OCI: Prepare(SELECT COUNT(*)<br>
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t<br>
WHERE u.table_name = :table_name<br>
AND u.column_name = :geometry_name)<br>
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))<br>
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER(:table_name) AND COLUMN_NAME = UPPER(:geometry_name))<br>
OCI: Prepare(SELECT COLUMN_NAME, DATA_DEFAULT<br>
FROM user_tab_columns<br>
WHERE DATA_DEFAULT IS NOT NULL AND TABLE_NAME = UPPER(:table_name))<br>
OCI: Prepare(SELECT COUNT(*)<br>
FROM USER_SDO_GEOM_METADATA u, TABLE(u.diminfo) t<br>
WHERE u.table_name = :table_name<br>
AND u.column_name = :geometry_name)<br>
OCI: Prepare(select m.SDO_LAYER_GTYPE from user_sdo_index_metadata m, user_sdo_index_info i where i.index_name = m.sdo_index_name and i.table_name = upper(:table_name))<br>
OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER(:table_name) AND COLUMN_NAME = UPPER(:geometry_name))<br>
GDAL: GDALOpen(OCI:system/ollikok90, this=000001618B8C0B20) succeeds as OCI.<br>
OCI: Prepare(INSERT /*+ APPEND */ INTO "TIME_TEST"("OGR_FID","ORA_GEOMETRY","aika","attr","column") VALUES ( :fid , :geometry, :field_0, :field_1, :field_2) )<br>
OCI: Prepare(SELECT MAX("OGR_FID") FROM "TIME_TEST")<br>
GDALVectorTranslate: 3 features written in layer 'TIME_TEST'<br>
Shape: 3 features read on layer 'timetest'.<br>
GDAL: GDALClose(timetest.shp, this=000001618B8A4540)<br>
OCI: Flushing 3 features on layer TIME_TEST<br>
OCI: Prepare(select min(case when r=1 then sdo_lb else null end) minx, min(case when r=2 then sdo_lb else null end) miny, min(case when r=1 then sdo_ub else null end) maxx, min(case when r=2 then sdo_ub else null end) maxy from (SELECT d.sdo_dimname, d.sdo_lb,
sdo_ub, sdo_tolerance, rownum r FROM ALL_SDO_GEOM_METADATA m, table(m.diminfo) d where m.table_name = UPPER('TIME_TEST') and m.COLUMN_NAME = UPPER('ORA_GEOMETRY') ) )<br>
<br>
-Jukka Rahkonen-<br>
<br>
-----Alkuperäinen viesti-----<br>
Lähettäjä: Kreß, Marius <<a href="mailto:KressM@eba.bund.de">KressM@eba.bund.de</a>>
<br>
Lähetetty: torstai 12. marraskuuta 2020 18.40<br>
Vastaanottaja: Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
Aihe: AW: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?<br>
<br>
Hi,<br>
<br>
I realized that I understood your first mail wrong. I read "Write your commands..." as "write them in the console" and wondered how this should work. But you meant "write them in the email", right? That makes more sense.<br>
<br>
The first command is:<br>
ogr2ogr OCI:(connection) "D:\(path)\isophone_6611.shp" -nln isophone_test This throws a few errors, but still works.<br>
<br>
Then the next command would be: <br>
ogr2ogr -update -append OCI:(connection) "D:\(path)\isophone_6612.shp" -nln isophone_test This throws similar errors to the one above, but one more:<br>
ERROR 1: ORA-00955: Es gibt bereits ein Objekt mit diesem Namen [which means: There is already an object with that name] in CREATE TABLE "ISOPHONE_TEST" ( OGR_FID INTEGER PRIMARY KEY, ORA_GEOMETRY MDSYS.SDO_GEOMETRY )<br>
<br>
That’s why I said it seems like the -update and -append options are ignored.<br>
<br>
Best regards,<br>
Marius Kress<br>
<br>
-----Ursprüngliche Nachricht-----<br>
Von: Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
Gesendet: Mittwoch, 11. November 2020 16:58<br>
An: Kreß, Marius <<a href="mailto:KressM@eba.bund.de">KressM@eba.bund.de</a>><br>
Betreff: Re: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?<br>
<br>
Hi,<br>
<br>
You wrote<br>
<br>
" Even if I first write it into a shapefile and then try to write it in the database with ogr2ogr, it ignores the -append and -update options."<br>
<br>
This ogr2ogr command is what I meant with the second command. The first one is the ogr2ogr command that you used when you created the Oracle table and inserted the first batch of data into it.<br>
<br>
-Jukka Rahkonen-<br>
<br>
-----Alkuperäinen viesti-----<br>
Lähettäjä: Kreß, Marius <<a href="mailto:KressM@eba.bund.de">KressM@eba.bund.de</a>><br>
Lähetetty: keskiviikko 11. marraskuuta 2020 17.53<br>
Vastaanottaja: Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
Aihe: AW: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?<br>
<br>
Hi Jukka,<br>
<br>
thank you very much for your reply! But unfortunately I don't understand what you mean. Could you maybe give me an example what the first two commands might look like?<br>
<br>
Best regards,<br>
Marius Kress<br>
<br>
-----Ursprüngliche Nachricht-----<br>
Von: gdal-dev <<a href="mailto:gdal-dev-bounces@lists.osgeo.org">gdal-dev-bounces@lists.osgeo.org</a>> Im Auftrag von jratike80<br>
Gesendet: Mittwoch, 11. November 2020 16:19<br>
An: <a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
Betreff: Re: [gdal-dev] Oracle Spatial Driver: Is it possible to append features to a table?<br>
<br>
Hi,<br>
<br>
It should work. Write your two first ogr2ogr commands (the initial one and first to append) as a whole but without OCI connection details. The -nln parameter must appear in the append command if I remember right.<br>
<br>
-Jukka Rahkonen-<br>
<br>
<br>
<br>
Kreß, Marius wrote<br>
> Dear GDAL developers and users,<br>
> <br>
> I want to write contour polygons into an Oracle Spatial database. <br>
> Because the area is quite large, I would like to calculate them in <br>
> smaller boxes and write them into the table one after the other. But <br>
> as far as I can see, it is not possible to append features to a table.<br>
> You can only create a new one, overwrite or truncate it. Even if I <br>
> first write it into a shapefile and then try to write it in the <br>
> database with ogr2ogr, it ignores the -append and -update options.<br>
> <br>
> Is it correct that it doesn't work or did I miss anything? Do you know <br>
> a better solution?<br>
> <br>
> Thanks for your help,<br>
> <br>
> Marius Kress<br>
> <br>
> _______________________________________________<br>
> gdal-dev mailing list<br>
<br>
> <a href="mailto:gdal-dev@.osgeo">gdal-dev@.osgeo</a><br>
<br>
> <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
<br>
<br>
<br>
<br>
<br>
--<br>
Sent from: <a href="http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html">http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html</a><br>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><o:p></o:p></span></p>
</div>
</div>
</body>
</html>