Questions tagged [oracle-dbms]
Use for questions about Oracle DBMS that are not about its Spatial or Locator extensions
354
questions
0
votes
0answers
11 views
Enable Archiving and Enable Editor Tracking on Oracle SDO
Are enable archiving and enable editor tracking allowed in Oracle database system (SDO)? Since I usually use PostgreSQL, I don't know the features supported by the Oracle database.
0
votes
1answer
33 views
GeoServer using read only database
Can GeoServer use an Oracle database that is in a read-only state or does it need the ability to generate its own indexes if existing databases do not have the necessary indexes already generated?
0
votes
0answers
12 views
Oracle query to Update parcels street column with nearest street line using Esri ST_GEOMETRY
I want to update the parcels street column with nearest street layer, from the below image P1 street column should update with L1 and P2 street column update with L2. Using ST_Distance and I got the ...
0
votes
1answer
43 views
SearchCursor RuntimeError: Underlying DBMS error [ORA-00923: FROM keyword not found where expected]
I need load OBJECTID from remote DB but I'm still getting error:
RuntimeError: Underlying DBMS error [ORA-00923: FROM keyword not found where expected] [SDEDO.E_EL_TU][STATE_ID = 22362]
I don't know ...
1
vote
1answer
52 views
Query for ST_Geometry line vertices via a recursive SQL WITH clause?
I'm exploring ways to get ST_Geometry line vertices as individual rows in a query resultset (via Oracle SQL).
The reason being, ST_Geometry doesn't have a GetVertices function (see ESRI idea here).
...
1
vote
2answers
73 views
ST_PointN equivalent in SDO_GEOMETRY?
In SDE.ST_GEOMETRY, we have a function called ST_PointN:
ST_PointN takes an ST_LineString and an integer index and returns a
point that is the nth vertex in the ST_LineString's path.
sde.st_pointn (...
1
vote
1answer
56 views
Scheduled/emailed reports using ArcGIS Notebook Server?
Is it possible to send scheduled/emailed reports via ArcGIS Notebook Server scheduled tasks?
An example of a scheduled report would be:
The report/task runs weekly
Emailed to a list of recipients (...
0
votes
0answers
41 views
Scheduled reports in ArcGIS Enterprise (emailed; condition-based)
ArcGIS Enterprise 10.7.1; Oracle 18c
I want to set up scheduled tabular reports based on Oracle GDB data:
The reports would run weekly on a server (not an individual's computer)
Emailed to a list of ...
0
votes
0answers
14 views
Editing featureclass in Oracle SDO_GEOMETRY
I'm building an empty featureclass in Oracle and want to enable editing in arcmap/py with having the featureclass user SDO_GEOMETRY for the shape column. However, I'm running into difficulties on how ...
1
vote
2answers
62 views
Update sister table with vertices as rows (when SHAPE is updated)
I have an Oracle 18c table that has an SDO_GEOEMTRY column.
create table a_test_sdo_geom (id number, shape mdsys.sdo_geometry);
insert into a_test_sdo_geom (id, shape)
values(1, sdo_util....
2
votes
2answers
84 views
Select vertices, including multi-part number
I have an Oracle 18c table with an SDO_GEOEMTRY column:
create table a_test_sdo_geom (shape mdsys.sdo_geometry);
The table has a row that is a multi-part feature.
insert into a_test_sdo_geom (shape)
...
0
votes
2answers
73 views
Select specific attribute from SDE.ST_Geometry UDT? (without using accessor functions)
Oracle 18c; ArcGIS 10.7.1
I have an SDE.ST_Geometry polyline feature class.
As an experiment/learning exercise, I am trying to learn how to interact directly with the user-defined type (UDT) SHAPE ...
2
votes
1answer
22 views
What densification method does SDE.ST_Geometry use to approximate arcs as segments?
Oracle 18c; ArcGIS 10.7.1
I have an SDE.ST_Geometry polyline FC that has arcs.
In a related post, we established that true arcs/curves are only available to ArcObjects, whereas the actual geometry ...
0
votes
1answer
16 views
Multi-part ST_GEOMETRY: Does the feature-part array start at 1 or 0?
Oracle 18c; ArcGIS 10.7.1
I have an SDE.ST_GEOMETRY polyline FC that has multi-part features.
Question:
What method does ArcGIS use to assign numbers to the feature-parts in the array?
Do the part ...
2
votes
1answer
80 views
Convert ST_POINT subtype to ST_GEOMETRY supertype?
Oracle 18c; ArcGIS 10.7.1
I have a query that produces an ST_POINT column:
--Mimics the SDO_GEOMETRY get_vertices function
--https://docs.oracle.com/database/121/SPATL/sdo_util-getvertices.htm
...
1
vote
1answer
45 views
Why does SDE.ST_NUMPOINTS() add extra vertices for lines with arcs?
Oracle 18c/10.7.1 EGDB:
I have a polyline FC with a feature that has an arc.
I want to count the number of vertices in the line using the SDE.ST_NUMPOINTS() function.
select
sde.st_numpoints(...
1
vote
2answers
45 views
Does SDE.ST_LENGTH() calculate the true length of arcs?
Oracle 18c/10.7.1 EGDB
I have a polyline FC that has features with arcs.
Does the Oracle SDE.ST_LENGTH() function calculate the true length of arcs?
Or does it only calculate the straight-line length ...
1
vote
0answers
26 views
Select SDE.ST_GEOMETRY polylines that have arcs?
Oracle 18c/10.7.1 EGDB:
I have an SDE.ST_GEOMETRY polyline feature class.
Some of the polylines in the feature class have arcs.
Question:
Is there a way to use Oracle SDE.ST_GEOEMTRY functions to ...
0
votes
1answer
59 views
ST_GEOMETRY: spatial column must appear immediately after spatial relationship function for spatial index to be used
The docs for SDE.ST_GEOMETRY have a blurb that says the following:
When are spatial indexes used with ST_Geometry?
...the spatial column must appear immediately after the
spatial relationship ...
1
vote
1answer
91 views
ST_GEOMETRY points: get attribute of intersecting polygon
I have an SDE.ST_GEOEMTRY point feature class.
I want to create an SQL query from the points. The query would perform a spatial join/intersection with an SDE.ST_GEOMETRY polygon feature class. An ...
0
votes
1answer
52 views
What system tables get INSERTS when registering object with Oracle EGDB (ST_Geometry)?
In ArcGIS Desktop, I can register ST_Geometry objects with the geodatabase (Enterprise 10.7.1; Oracle 18c):
Register with Geodatabase
Register feature classes, tables, views, and raster layers that ...
2
votes
1answer
90 views
Function-based spatial index: Use sdo_geometry() constructor instead of custom function?
I have an Oracle 18c table that has XY columns, but not a SHAPE column. I've created a query on the table that dynamically generates an sdo_geometry SHAPE column.
sdo_geometry(2001, 26917, ...
3
votes
5answers
214 views
Materialized view with FAST refresh on remote table: How to include a SHAPE column?
I want to create an Oracle materialized view with the fast refresh option on a remote table (in an enterprise GDB).
I can do this successfully without a SHAPE column:
create materialized view log on ...
3
votes
1answer
133 views
QGIS access Oracle non-spatial database tables
I stumbled accross this when I tried to connect to a "normal" Oracle 11g instance (no Oracle Spatial) in order to join Oracle tables to spatial data in other formats. Note that this is not ...
3
votes
1answer
47 views
Use multiprocessing to speed up rebuild index in ArcPy
We are in ArcGIS 10.6.1 (on Windows Server 2012) and Enterprise GeoDatabase Oracle 19c (on Linux).
I was wondering if it is feasible to use the Python's ProcessPoolExecutor to open two separate Oracle ...
0
votes
1answer
74 views
SDO_GEOM materialized view: Why is static field slower than dynamic field?
EGDB: Oracle 18c; ArcGIS 10.7.1
External system: Oracle 12c
I have a materialized view in an EGDB that syncs data from an external system (on a schedule via a dblink).
The materialized view has an ...
0
votes
0answers
68 views
Materialized view breaks when registered with the GDB
EGDB: Oracle 18c/10.7.1
External system: Oracle 12c
I have created a non-spatial materialized view in an EGDB that syncs data from an external system (on a 5 second schedule; via a dblink).
It is ...
0
votes
2answers
38 views
Sync changes from view to feature class (using a GP tool; without fully replacing the data)
I have an ST_GEOMETRY view with 50,000 rows (in an Oracle 18c/10.7.1 geodatabase). The view references a WORKORDER table in an external system (via a dblink/Oracle 12c).
Problem:
The view is too slow ...
0
votes
1answer
16 views
Prevent users from deleting EGDB tables [closed]
Oracle 18c/10.7.1 geodatabase:
My geodatabase has an owner/schema/user called OPERATIONS.
Operations staff have a direct db connection in ArcMap/Catalog to the OPERATIONS owner. This connection is ...
1
vote
0answers
147 views
I need to remove Z dimension on spatial column (ST_GEOMETRY) with Esri on Oracle using the PL/SQL packages
I need to remove Z dimension on spatial column (ST_GEOMETRY) with Esri on Oracle using the PL/SQL packages
I need to do the following, but using the ESRI PL/SQL packages on Oracle.
I have used a ...
0
votes
1answer
42 views
Under what circumstances would the _DELETE trigger get called? (_EVW)
Oracle 18c/10.7.1 geodatabase:
When an FC is registered as versioned (with option to move edits to base), several triggers get automatically created:
V000001_DELETE
V000001_INSERT
V000001_UPDATE
...
2
votes
2answers
110 views
Prevent deletes in a versioned table at the database level
I have a table in an Oracle 18c/10.7.1 geodatabase. The table is versioned with the option to move edits to base.
As far as I can tell, it's not possible to prevent ArcGIS Desktop users from deleting ...
2
votes
1answer
372 views
Calculate irregular polygon center-point that falls within the polygon boundary
I have an SDE.ST_GEOMETRY polygon FC in an Oracle 18c/10.7.1 geodatabase.
I can calculate the X and Y of the polygon centroids using the SDE.ST_GEOMETRY ST_Centroid function:
sde.st_x(sde....
0
votes
2answers
67 views
Creating a view in ArcCatalog - ORA-01722 [closed]
I'm creating a view in ArcCatalog 10.6 on an Oracle DB. Never created a view before but with some Googling, I've managed the SQL below. The view works but only partially. In ArcCatalog, preview shows ...
1
vote
1answer
981 views
How to find closest point on a line from a point in Oracle Spatial?
I want to find the nearest point on a line from a point in Oracle Spatial. I know how to do in SQL Server and PostgreSQL but could not find anything about Oracle.
I have used MDSYS.sdo_nn to find the ...
1
vote
3answers
1k views
Converting SDE.ST_GEOMETRY polyline to SDO_GEOMETRY with SQL: Why are there so many nulls?
I have a SDE.ST_GEOMETRY column in an Oracle 18c/10.7.1 geodatabase.
I want to convert the SDE.ST_GEOMETRY shape column to SDO_GEOMETRY using SQL.
Steps:
Convert the SDE.ST_GEOMETRY shape column to ...
1
vote
1answer
265 views
Use SDE.ST_GEOMETRY functions in a custom function
I have a dummy query where I generate an SDE.ST_GEOMETRY point:
select
shape as srid_test
from
(
select
sde.st_geometry ('point (10.01 50.76)', 4326) as shape
from
...
2
votes
1answer
181 views
How to filter date values using OGR 2.4.0?
I am trying to replicate a working Oracle SQL query into ogr2ogr, but having issues with a date field where clause.
My query is:
select BLOCK_STATUS_DATE, GEOMETRY
from WHSE_FOREST_TENURE....
2
votes
1answer
545 views
Get midpoint of SDE.ST_GEOMETRY polylines?
I have a polyline SDE.ST_GEOMETRY feature class in an Oracle 12c geodatabase (10.7.1).
I want to create a view on the feature class that has X and Y columns that contain the coordinates of the ...
0
votes
0answers
111 views
No Oracle Button in QGIS Data Source Manager on Linux
I'm using QGIS 3.10 on a system with Fedora 30 Linux.
In the Data Source Manager is the button for Oracle-database-connections missing. An installation of the same QGIS-version on a Windows-Machine ...
1
vote
1answer
73 views
Extract subtype codes and descriptions in a view
I have an Oracle 10.7.1 geodatabase that has tables and feature classes with subtypes.
How can I extract the codes and descriptions from the subtypes in a view?
0
votes
1answer
41 views
Converted timezone does not display properly in ArcMap
(Oracle 12c geodatabase)
I have a view that converts a UTC timestamp to Eastern Time using the from_tz function:
create or replace view test_vw as (
select
cast(rownum as number(38,0)) as rownum_,
...
1
vote
1answer
78 views
SDO_JOIN conversion to PostGIS
I make refactoring from Oracle to PostGIS and I found out that PostGIS doesn't support sdo_join.
How should I correct the statement below?
SELECT e.ID AS EDGE1_ID,
u.NODE_ID AS ...
6
votes
1answer
178 views
ArcPy UpdateCursor auto pausing after every 1000 records
I have an update cursor running that updates a field based on a lookup table (Python dictionary). The cursor completes fine, but seems to pause and hold after each 1000 records - I have a test in it ...
0
votes
0answers
93 views
WHERE clause that's stored in a table: Use it to filter a layer?
I have a workorder management system (WMS).
The WMS has functionality that allows users to save queries.
The queries are saved in a Oracle database table called QUERY (in a CLAUSE column).
An ...
0
votes
1answer
118 views
Selecting the SHAPE column in Toad
I've noticed that SQL clients select the SHAPE column from SDE.ST_GEOMETRY in different ways:
select shape from roads
Toad: SDE.ST_GEOMETRY(1,1,666666.0,4444444.5,666666.0,4444444.5,NULL,NULL,NULL,...
0
votes
0answers
346 views
ST_TRANSFORM SDE Oracle does not convert the geometry
I try to convert a geometry from 31370 to 3857. But it does not work. The coordinates stay in 31370.
DB: Oracle 11
ArcSDE: 10.4.1
The code:
select
sde.st_srid(shape)
,sde.st_x(shape) as ...
0
votes
0answers
45 views
Select domain's last update date
I have tables in an Oracle 18c GDB that I integrate to a work order management system.
I create views on the tables to translate the data. For example, I translate domain codes to domain descriptions.
...
1
vote
2answers
167 views
Prevent row deletion in versioned tables using database privileges
I have versioned tables in an Oracle geodatabase (versioned with the option to move edits to base).
I would like to prevent users from deleting rows from the tables when using ArcGIS Desktop.
(With ...
2
votes
0answers
147 views
Connecting QGIS to Oracle?
I am using QGIS 3.8 MacOS.
I know that prior releases of QGIS (2.x releases) did not include drivers to connect to Oracle databases, and one would need to compile QGIS themselves with the proper ...
