nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle View - two columns remain empty in ArcMap (but not in SQL Developer)

robert_at_work

Hi there,

I have created a view in Oracle 11.2.. Its a view across three tables. I have contracts in one table and people related to the contracts in a second table (1:n). I need the people's names in a field next to the contract details (for export purposes only). Therefor I created a view using LISTAGG to concatenate all people's names related to a contract.

Works like a charm when viewed with SQL Developer. Unfortunately the column with the people’s names remains empty when viewed in ArcCatalog or opened in ArcMap. Its a table without any geometry. Any idea why just that single column remains empty? I casted the column type nvarchar2(500) - same data type and length as the column in the original table (which of course is displayed in ArcMap).

ArcMap 10.5.1, Win 10.

many thanks, Rob

Find more posts tagged with

Accepted answers

robert_at_work

Hi there,

I could solve the problem by doing a cast with "to_char". The LISTAGG command looks like that now:

cast<SPAN class="punctuation token">(</SPAN>listagg<SPAN class="punctuation token">(</SPAN>to_char<SPAN class="punctuation token">(</SPAN>nachname<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">||</SPAN> <SPAN class="string token">'; '</SPAN> <SPAN class="operator token">||</SPAN> to_char<SPAN class="punctuation token">(</SPAN>vorname<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' / '</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">within</SPAN> <SPAN class="keyword token">group</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">order</SPAN> <SPAN class="keyword token">by</SPAN> nachname<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> varchar2<SPAN class="punctuation token">(</SPAN><SPAN class="number token">4000</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> eigentuemer_name<SPAN class="punctuation token">,</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Further details can be found on my blog.

Rob

All comments

CraigSwadner

Attach the View code…

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

robert_at_work

Oracle View Code - see below. I also tried a different seperator but it doesnt change the result.

Screenshot SQL developer sql delevoper

<SPAN class="keyword token">create</SPAN> <SPAN class="operator token">or</SPAN> replace <SPAN class="keyword token">view</SPAN> vmg_v_dbv_vertrag_eig <SPAN class="keyword token">as</SPAN>
<SPAN class="keyword token">select</SPAN> 
objectid<SPAN class="punctuation token">,</SPAN>
globalid<SPAN class="punctuation token">,</SPAN>
dokument_nr<SPAN class="punctuation token">,</SPAN> 
<SPAN class="keyword token">status</SPAN><SPAN class="punctuation token">,</SPAN> 
beschreibung<SPAN class="punctuation token">,</SPAN> 
vertragsbeginn<SPAN class="punctuation token">,</SPAN> 
vertragsende<SPAN class="punctuation token">,</SPAN> 
entschaedigungsende<SPAN class="punctuation token">,</SPAN>
geltung<SPAN class="punctuation token">,</SPAN>
grundbuch<SPAN class="punctuation token">,</SPAN>
typ<SPAN class="punctuation token">,</SPAN>
art<SPAN class="punctuation token">,</SPAN>
gemeinde<SPAN class="punctuation token">,</SPAN>
gemeinde_aktuell<SPAN class="punctuation token">,</SPAN>
bfs_nr<SPAN class="punctuation token">,</SPAN>
parzelle_nr<SPAN class="punctuation token">,</SPAN>
berechtigte_1<SPAN class="punctuation token">,</SPAN>
berechtigte_2<SPAN class="punctuation token">,</SPAN>
berechtigte_3<SPAN class="punctuation token">,</SPAN>
berechtigte_4<SPAN class="punctuation token">,</SPAN>
ersteller_1<SPAN class="punctuation token">,</SPAN>
ersteller_2<SPAN class="punctuation token">,</SPAN>
ersteller_3<SPAN class="punctuation token">,</SPAN>
ersteller_4<SPAN class="punctuation token">,</SPAN>
trasse_elemente<SPAN class="punctuation token">,</SPAN>
spannung_1<SPAN class="punctuation token">,</SPAN>
spannung_2<SPAN class="punctuation token">,</SPAN>
spannung_3<SPAN class="punctuation token">,</SPAN>
spannung_4<SPAN class="punctuation token">,</SPAN>
archivbox<SPAN class="punctuation token">,</SPAN>
cast<SPAN class="punctuation token">(</SPAN>listagg<SPAN class="punctuation token">(</SPAN>nachname <SPAN class="operator token">||</SPAN> <SPAN class="string token">'; '</SPAN> <SPAN class="operator token">||</SPAN> vorname <SPAN class="operator token">||</SPAN> <SPAN class="string token">' / '</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">within</SPAN> <SPAN class="keyword token">group</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">order</SPAN> <SPAN class="keyword token">by</SPAN> nachname<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> nvarchar2<SPAN class="punctuation token">(</SPAN><SPAN class="number token">500</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> eigentuemer_name<SPAN class="punctuation token">,</SPAN> 
cast<SPAN class="punctuation token">(</SPAN>listagg<SPAN class="punctuation token">(</SPAN>plz <SPAN class="operator token">||</SPAN> <SPAN class="string token">'; '</SPAN> <SPAN class="operator token">||</SPAN> wohnort <SPAN class="operator token">||</SPAN> <SPAN class="string token">' / '</SPAN> <SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">within</SPAN> <SPAN class="keyword token">group</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">order</SPAN> <SPAN class="keyword token">by</SPAN> nachname<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> nvarchar2<SPAN class="punctuation token">(</SPAN><SPAN class="number token">500</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> eigentuemer_plz_ort 
<SPAN class="keyword token">from</SPAN>
<SPAN class="punctuation token">(</SPAN>
<SPAN class="keyword token">select</SPAN> 
v<SPAN class="punctuation token">.</SPAN>objectid<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>globalid<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>dokument_nr<SPAN class="punctuation token">,</SPAN> 
v<SPAN class="punctuation token">.</SPAN><SPAN class="keyword token">status</SPAN><SPAN class="punctuation token">,</SPAN> 
v<SPAN class="punctuation token">.</SPAN>beschreibung<SPAN class="punctuation token">,</SPAN> 
v<SPAN class="punctuation token">.</SPAN>vertragsbeginn<SPAN class="punctuation token">,</SPAN> 
v<SPAN class="punctuation token">.</SPAN>vertragsende<SPAN class="punctuation token">,</SPAN> 
v<SPAN class="punctuation token">.</SPAN>entschaedigungsende<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>geltung<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>grundbuch<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>typ<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>art<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>gemeinde<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>gemeinde_aktuell<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>bfs_nr<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>parzelle_nr<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>berechtigte_1<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>berechtigte_2<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>berechtigte_3<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>berechtigte_4<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>ersteller_1<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>ersteller_2<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>ersteller_3<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>ersteller_4<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>trasse_elemente<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>spannung_1<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>spannung_2<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>spannung_3<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>spannung_4<SPAN class="punctuation token">,</SPAN>
v<SPAN class="punctuation token">.</SPAN>archivbox<SPAN class="punctuation token">,</SPAN>
ve<SPAN class="punctuation token">.</SPAN>vertrag_ref<SPAN class="punctuation token">,</SPAN> 
ve<SPAN class="punctuation token">.</SPAN>eigentuemer_ref<SPAN class="punctuation token">,</SPAN>  
<SPAN class="number token">e</SPAN><SPAN class="punctuation token">.</SPAN>nachname<SPAN class="punctuation token">,</SPAN> 
<SPAN class="number token">e</SPAN><SPAN class="punctuation token">.</SPAN>vorname<SPAN class="punctuation token">,</SPAN> 
<SPAN class="number token">e</SPAN><SPAN class="punctuation token">.</SPAN>plz<SPAN class="punctuation token">,</SPAN>
<SPAN class="number token">e</SPAN><SPAN class="punctuation token">.</SPAN>wohnort

<SPAN class="keyword token">from</SPAN> vmg_dbv_vertrag v <SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmgr_vertrag_eigentuemer ve <SPAN class="keyword token">on</SPAN> v<SPAN class="punctuation token">.</SPAN>globalid <SPAN class="operator token">=</SPAN> ve<SPAN class="punctuation token">.</SPAN>vertrag_ref
<SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmg_eigentuemer <SPAN class="number token">e</SPAN> <SPAN class="keyword token">on</SPAN> ve<SPAN class="punctuation token">.</SPAN>eigentuemer_ref <SPAN class="operator token">=</SPAN> <SPAN class="number token">e</SPAN><SPAN class="punctuation token">.</SPAN>globalid
<SPAN class="comment token">--where v.dokument_nr = 'DBV013981'</SPAN>
<SPAN class="comment token">--order by v.dokument_nr</SPAN>
<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">group</SPAN> <SPAN class="keyword token">by</SPAN> 
objectid<SPAN class="punctuation token">,</SPAN>
globalid<SPAN class="punctuation token">,</SPAN>
dokument_nr<SPAN class="punctuation token">,</SPAN> 
<SPAN class="keyword token">status</SPAN><SPAN class="punctuation token">,</SPAN> 
beschreibung<SPAN class="punctuation token">,</SPAN> 
vertragsbeginn<SPAN class="punctuation token">,</SPAN> 
vertragsende<SPAN class="punctuation token">,</SPAN> 
entschaedigungsende<SPAN class="punctuation token">,</SPAN>
geltung<SPAN class="punctuation token">,</SPAN>
grundbuch<SPAN class="punctuation token">,</SPAN>
typ<SPAN class="punctuation token">,</SPAN>
art<SPAN class="punctuation token">,</SPAN>
gemeinde<SPAN class="punctuation token">,</SPAN>
gemeinde_aktuell<SPAN class="punctuation token">,</SPAN>
bfs_nr<SPAN class="punctuation token">,</SPAN>
parzelle_nr<SPAN class="punctuation token">,</SPAN>
berechtigte_1<SPAN class="punctuation token">,</SPAN>
berechtigte_2<SPAN class="punctuation token">,</SPAN>
berechtigte_3<SPAN class="punctuation token">,</SPAN>
berechtigte_4<SPAN class="punctuation token">,</SPAN>
ersteller_1<SPAN class="punctuation token">,</SPAN>
ersteller_2<SPAN class="punctuation token">,</SPAN>
ersteller_3<SPAN class="punctuation token">,</SPAN>
ersteller_4<SPAN class="punctuation token">,</SPAN>
trasse_elemente<SPAN class="punctuation token">,</SPAN>
spannung_1<SPAN class="punctuation token">,</SPAN>
spannung_2<SPAN class="punctuation token">,</SPAN>
spannung_3<SPAN class="punctuation token">,</SPAN>
spannung_4<SPAN class="punctuation token">,</SPAN>
archivbox<SPAN class="punctuation token">;</SPAN>s<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

robert_at_work

Hi there,

I could solve the problem by doing a cast with "to_char". The LISTAGG command looks like that now:

cast<SPAN class="punctuation token">(</SPAN>listagg<SPAN class="punctuation token">(</SPAN>to_char<SPAN class="punctuation token">(</SPAN>nachname<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">||</SPAN> <SPAN class="string token">'; '</SPAN> <SPAN class="operator token">||</SPAN> to_char<SPAN class="punctuation token">(</SPAN>vorname<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">,</SPAN> <SPAN class="string token">' / '</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">within</SPAN> <SPAN class="keyword token">group</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">order</SPAN> <SPAN class="keyword token">by</SPAN> nachname<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> varchar2<SPAN class="punctuation token">(</SPAN><SPAN class="number token">4000</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> eigentuemer_name<SPAN class="punctuation token">,</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Further details can be found on my blog.

Rob

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.