I have the geoJson of this spatial data (Source: https://cartomad-ae-cirad.hub.arcgis.com/datasets/a761169493dd4cbfb2cab7d381cf2945_0/explore).
The points that constitute each polygons of each multipolygons is really a lot.
This is my python script that insert the data into my oracle database:
id = regs[each["properties"]["REGION"].upper()]["id"]
prov_id = regs[each["properties"]["REGION"].upper()]["PROV_ID"]
p_code = regs[each["properties"]["REGION"].upper()]["P_CODE"]
r_code = regs[each["properties"]["REGION"].upper()]["R_CODE"]
region = each["properties"]["REGION"].upper()
script = """
INSERT INTO regions(id, id_province, p_code, r_code, nom, geom)
VALUES(""" + id + """, """ + prov_id + """,'""" + p_code + """','""" + r_code + """','""" + region + """', SDO_GEOMETRY(
2007,
8307,
NULL,
SDO_ELEM_INFO_ARRAY(""" + sd_elem_info[2] + """),
SDO_ORDINATE_ARRAY(""" + str_coordinates + """)
))
"""
cursor.execute(script)
I always get this error:
Traceback (most recent call last):
File “d:Stagemdg_shp_trustedregion.py”, line 111, in cursor.execute(script)
File “C:Python312Libsite-packagesoracledbcursor.py”, line 710, in execute impl.execute(self)
File “srcoracledbimpl/thin/cursor.pyx”, line 196, in oracledb.thin_impl.ThinCursorImpl.execute
File “srcoracledbimpl/thin/protocol.pyx”, line 440, in oracledb.thin_impl.Protocol._process_single_message
File “srcoracledbimpl/thin/protocol.pyx”, line 441, in oracledb.thin_impl.Protocol._process_single_message
File “srcoracledbimpl/thin/protocol.pyx”, line 433, in oracledb.thin_impl.Protocol._process_message
File “srcoracledbimpl/thin/messages.pyx”, line 74, in oracledb.thin_impl.Message._check_and_raise_exception
oracledb.exceptions.DatabaseError: ORA-00939: trop d’arguments pour la fonction
Help: https://docs.oracle.com/error-help/db/ora-00939/
It is saying that there is too much parameters passed to the function (I am not really sure that maybe it is because of the number of points representing the whole multipolygons).
I have already tried to pass arguments like using a prepared statement (:name, {‘name’: value}) but the same error.
I was wondering if you guys know a better way to insert such data.
Thanks in advance.
w41k4z is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
For security and scalability you must use prepared statements with bind variables, as you first tried.
Regarding the error, you don’t quite give enough info but guessing that the SDO routines are the cause, look at https://github.com/oracle/python-oracledb/blob/main/samples/insert_geometry.py
type_obj = connection.gettype("MDSYS.SDO_GEOMETRY")
element_info_type_obj = connection.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinate_type_obj = connection.gettype("MDSYS.SDO_ORDINATE_ARRAY")
obj = type_obj.newobject()
obj.SDO_GTYPE = 2003
obj.SDO_ELEM_INFO = element_info_type_obj.newobject()
obj.SDO_ELEM_INFO.extend([1, 1003, 3])
obj.SDO_ORDINATES = ordinate_type_obj.newobject()
obj.SDO_ORDINATES.extend([1, 1, 5, 7])
print("Created object", obj)
with connection.cursor() as cursor:
cursor.execute("truncate table TestGeometry")
print("Adding row to table...")
cursor.execute("insert into TestGeometry values (1, :objbv)", objbv=obj)
connection.commit()
print("Success!")
1