ArcMap – Oracle LISTAGG results in empty column

When using LISTAGG recently in view definition I had the problem that the column content based on LISTAGG was not shown in ArcMap / ArcCatalog.

The LISTAGG command usage was like that:

…

cast(listagg(nachname || '; ' || vorname || ' / ') within group(order by nachname) as nvarchar2(500)) as eigentuemer_name,

…

The columns (nachname, vorname) I concatenated were of data type “nvarchar2”. My initial view definition did not specify the data type for the column – I added it later but it did not solve my problem.

In SQL Developer the content of the column was shown right from the start. To check how other applications behave I connected to the view using Excel and tried to import the data into a sheet. When I did that I got an ORA-01401 inserted value too large for column message. After bit of research I found the following discussion on SO.

It was mentioned that there might be an issue with the length of the right hand side of the concatenation operator when usung NVARCHAR. I changed the view definition to VARCHAR2 and also increased the size. Afterwards I could import the data into Excel – but in ArcMap the column was still empty.

The second hint on the page – applying to_char conversion - finally solved the issue:

...

cast(listagg(to_char(nachname) || '; ' || to_char(vorname) , ' / ') within group(order by nachname) as varchar2(4000)) as eigentuemer_name,

...

It’s a bit strange – other columns in the view based on NVARCHAR2 are shown in ArcMap. Only in connection with LISTAGG the problem occurs.

ArcMap 10.5.1, Oracle 11.2.0.4.0

Oracle ANSI JOIN syntax improves peformance

Using ANSI JOIN syntax instead of Oracle Join snytax is recommended - see here:

http://www.orafaq.com/node/2618

We found out the hard way:

- a third party application issues simple but nested SQL statements against Map IM views
- for some views we noticed that the query ran for a long time and generated a time out 
- just by changing the JOIN syntax to ANSI join the issue was resolved

Map 2017

Why you should restart AIMS/MapGuide when testing ....

I noticed by chance that a few layers - which are all based on one Oracle view - were significantly slower in our production system but no performance issue occurred in our test environment. Whilst investigating the issue I made a few mistakes which made the whole process of finding and fixing the issue more time consuming than necessary. Here are the details:

After noticing the issue I asked my colleagues if they had any idea why this is and whether anything had changed recently. None of them had an explanation so I spent some time checking / testing:

- running performance reports in MapGuide/AIMS noticing that time spent on "Layer" is 20times slower in production
- comparing view definitions but only cursorily (looking at WHERE clause only) : mistake #1
- re-creating spatial index for base table : no change
- running 1-click maintenance in Map-Administrator : no change
- copy a layer definition from production to test environment : no change
- trying to load view into other applications to see whether the issue is related to application or to view but GeoRaptor couldn't add layer to map due to rowid issue, QGIS rejected view complaining that view didn't have a proper spatial index and TatukGIS Viewer basically saying something similar (first the message about the spatial index issue made me re-re-create the spatial index but testing further views in QGIS/TatukGISViewer I got the same messages for all other views as well).
- running a few spatial SQL queries in SQLDeveloper but there wasn't any significant difference between test and production : mistake # 2 (this result caused quite some confusion as I did not have an explanation, it did not fit and even contradicted other findings!)

After spending some time on running these tests a colleague came back to me and told me, that the view had changed recently but when he applied the change he didn't notice any performance issue whilst generating graphics in Map. The change was made by adding a subquery to the SELECT clause. He asked if the change should be removed but I replied that I will do some tests now taking the new information into account : mistake # 3.

As the change to the view definition had only been applied in the production system I applied the change in the test system as well - here mistake # 4 occurred. I then ran the MapGuide/AIMS performance report again but the performance had not decreased. Performance was good as before. Then I changed the data source of the Oracle user I was testing with and pointed it to our production database. When I ran the test again performance was slow. I stopped any further testing for the day and did something else.

At this point my theory was that the same query has different execution plans in Oracle (production and test) therefore different execution times due to different Oracle settings. What didn't really fit was that when I ran the spatial queries in SQLDeveloper I didn't see any significant difference in performance. But I didn't have anything else to test so finally I decided to replace the view definition in production with the previous view definition - basically removing the change which had been applied recently. I re-ran the performance report in MapGuide/AIMS and suddenly I got an error message:

Failed to stylize layer: DP_xxx
An exception occurred in FDO component.
ORA-00904: "G"."OWNER_OBJTYP": invalid identifier
...

Now I realised one of my mistakes. MapGuide/AIMS caches certain settings/definitions. I don't know how it works in detail but probably roughly like that:
- when AIMS/MapGuide starts it reads the Oracle schema and caches some information
- (SELECT) statements are being build based on the cached information and used against Oracle

For a view MapGuide/AIMS seems to read the view definition and then creates a SELECT statement which includes all columns of the view. 
If the view definition in Oracle changes and new columns are added to the view MapGuide can still run the SELECT statement against the new view definition - basically just ignoring all newly added columns.  But when columns have been removed from the view the SELECT statement still includes those columns and Oracle will response with ora-00904.

Mistake 4 - when I applied the change to view definition in the test system I did not restart MapGuide/AIMS. MapGuide was still using the SELECT statement which did not include the newly added column in the view. But exactly that new column had a negative impact on performance.
If I had restarted MapGuide/AIMS at this point in time I could have avoided further testing and head-scratching.

Mistake 3 - if I had listened to my colleague who offered to remove the change in production I might have noticed the cause of the problem earlier (because even without restarting MapGuide/AIMS we would have seen the Ora-00904 message in the log file).

Mistake 2 - my spatial query was simplified, the select clause included only "fid, geom". As it did not include the column which caused the performance issue there is really no surprise that performance in both production and test were nearly the same. Conclusion: always use (if possible) exactly the same (SELECT) statement as the application when you do testing!

Mistake 1 - I should have noticed the difference in view definitions myself just by copying the view definitions from production and test into an editor and then noticing the different numbers of lines....

So at the end a trivial issue but it took longer then necessary to get it fixed.

Why you should restart AIMS/MapGuide when testing ....

I noticed by chance that a few layers - which are all based on one Oracle view - were significantly slower in our production system but no performance issue occurred in our test environment. Whilst investigating the issue I made a few mistakes which made the whole process of finding and fixing the issue more time consuming than necessary. Here are the details:

After noticing the issue I asked my colleagues if they had any idea why this is and whether anything had changed recently. None of them had an explanation so I spent some time checking / testing:

- running performance reports in MapGuide/AIMS noticing that time spent on "Layer" is 20times slower in production
- comparing view definitions but only cursorily (looking at WHERE clause only) : mistake #1
- re-creating spatial index for base table : no change
- running 1-click maintenance in Map-Administrator : no change
- copy a layer definition from production to test environment : no change
- trying to load view into other applications to see whether the issue is related to application or to view but GeoRaptor couldn't add layer to map due to rowid issue, QGIS rejected view complaining that view didn't have a proper spatial index and TatukGIS Viewer basically saying something similar (first the message about the spatial index issue made me re-re-create the spatial index but testing further views in QGIS/TatukGISViewer I got the same messages for all other views as well).
- running a few spatial SQL queries in SQLDeveloper but there wasn't any significant difference between test and production : mistake # 2

After spending some time on running these tests a colleague came back to me and told me, that the view had changed recently but when he applied the change he didn't notice any performance issue whilst generating graphics in Map. The change was made by adding a subquery to the SELECT clause. He asked if the change should be removed but I replied that I will do some tests now taking the new information into account : mistake # 3.

As the change to the view definition had only been applied in the production system I applied the change in the test system as well - here mistake # 4 occurred. I then ran the MapGuide/AIMS performance report again but the performance had not decreased. Performance was good as before. Then I changed the data source of the Oracle user I was testing with and pointed it to our production database. When I ran the test again performance was slow. I stopped any further testing for the day and did something else.

At this point my theory was that the same query has different execution plans in Oracle (production and test) therefore different execution times due to different Oracle settings. What didn't really fit was that when I ran the spatial queries in SQLDeveloper I didn't see any significant difference in performance. But I didn't have anything else to test so finally I decided to replace the view definition in production with the previous view definition - basically removing the change which had been applied recently. I re-ran the performance report in MapGuide/AIMS and suddenly I got an error message:

Failed to stylize layer: DP_xxx
An exception occurred in FDO component.
ORA-00904: "G"."OWNER_OBJTYP": invalid identifier
...

Now I realised one of my mistakes. MapGuide/AIMS caches certain settings/definitions. I don't know how it works in detail but probably roughly like that:
- when AIMS/MapGuide starts it reads the Oracle schema and caches some information
- (SELECT) statements are being build based on the cached information and used against Oracle

For a view MapGuide/AIMS seems to read the view definition and then creates a SELECT statement which includes all columns of the view. 
If the view definition in Oracle changes and new columns are added to the view MapGuide can still run the SELECT statement against the new view definition - basically just ignoring all newly added columns.  But when columns have been removed from the view the SELECT statement still includes those columns and Oracle will response with ora-00904.

Mistake 4 - when I applied the change to view definition in the test system I did not restart MapGuide/AIMS. MapGuide was still using the SELECT statement which did not include the newly added column in the view. But exactly that new column had a negative impact on performance.
If I had restarted MapGuide/AIMS at this point in time I could have avoided further testing and head-scratching.

Mistake 3 - if I had listened to my colleague who offered to remove the change in production I might have noticed the cause of the problem earlier (because even without restarting MapGuide/AIMS we would have seen the Ora-00904 message in the log file).

Mistake 2 - my spatial query was simplified, the select clause included only "fid, geom". As it did not include the column which caused the performance issue there is really no surprise that performance in both production and test were nearly the same. Conclusion: always use (if possible) exactly the same (SELECT) statement as the application when you do testing!

Mistake 1 - I should have noticed the difference in view definitions myself just by copying the view definitions from production and test into an editor and then noticing the different numbers of lines....

So at the end a trivial issue but it took longer then necessary to get it fixed.

AutoCAD Map 2017, job enabled IMs and Oracle 12.1.0.2.0

Map 2017 ReadMe states:

• Oracle 12cR1 (12.1.0.2.0) is not recommended for Industry Models with jobs. You should use other versions of Oracle like 11.2.0.4.0 or 12.1.0.1.0 to work with jobs.

This issue with Jobs and 12.1.0.2.0 has been fixed now by Autodesk.  Autodesk  has published a TS regarding the solution.

FME - Oracle Spatial to SDF

I just discovered an issue when converting Oracle Spatial data to SDF with FME. 

FME translates Oracle's NUMBER(1,0) to DECIMAL(1,0). In Oracle the column contains the following values: 0, 1, <null>. FME translates  value "1" as "-1e+031". One needs to change the data type in SDF file from DECIMAL to INT to avoid this issue.

FME 2014 SP4, 64bit

SDF file - column data type 

...value '1'' translated to -1e+031.

create view with unique FID

We have an Oracle view where no unique FID can be retrieved. Without an unique FID Map will not display the data in a form (industry model).

In our situation we only need a unique ID for the sake of the FID requirement by Map. To generate such a unique ID we chose the following approach:

- the view is wrapped by a select statement which includes rownum:

 create or replace view my_view as select  
 rownum fid, t.* from  
 (   
  ... view definition ...  
  ) t;  

As we need to show the data of the view only occasionally performance is not a major concern here.

Map 2013, SP2