tag:blogger.com,1999:blog-275898192024-03-13T11:00:11.721+01:00OraSalAndrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.comBlogger134125tag:blogger.com,1999:blog-27589819.post-7441742560199748052021-07-21T15:22:00.005+02:002021-07-24T11:16:55.336+02:00Analytic Functions preserve the "PARTITION BY" clause (Part 6): LAST_VALUE explained<p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_48.html"><< Part05</a> <br /></p><p>LAST_VALUE is an analytic function. <a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/LAST_VALUE.html#GUID-A646AF95-C8E9-4A67-87BA-87B11AEE7B79" target="_blank">Here</a> there is the documentation.</p><p><i><code class="codeph">"LAST_VALUE</code> is an analytic function that is useful for data densification. It returns the last value in an ordered set of values. </i></p><p><i>If the last value in the set is null, then the function returns <code class="codeph">NULL</code> unless you specify <code class="codeph">IGNORE</code> <code class="codeph">NULLS</code>. If you specify <code class="codeph">IGNORE NULLS</code>, then <code class="codeph">LAST_VALUE</code> returns the last non-null value in the set, or <code class="codeph">NULL</code> if all values are null.</i></p><p><i>The default is <code class="codeph">RESPECT</code> <code class="codeph">NULLS</code>."</i></p><p>The first thing, is to show how LAST_VALUE works using RESPECT NULLS (for simplicity I'm going to use only 3 columns)</p><p><span style="font-family: courier;">with<br />-----------<br />fix_1_ as (<br />-----------<br />SELECT<br /> elaboration_day,<br /> machine_id,<br /> lag(location_type) over (partition by machine_id order by elaboration_day) next_r,<br /> case<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) is null<br /> then elaboration_day<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type<br /> then elaboration_day<br /> else null<br /> end fix,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))<br />-------------<br />, fix_2_ as (<br />-------------<br />SELECT<br /> elaboration_day,<br /> <b>LAST_VALUE (fix) RESPECT NULLS<br /> OVER (PARTITION BY machine_id ORDER BY elaboration_day <br /> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix</b>,<br /> location_type<br />FROM<br /> fix_1_)<br />----------------------<br />select<br /> *<br /> from fix_2_<br /> order by elaboration_day;</span></p><p><span style="font-family: courier;"> </span></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUkLG0-EjrK7rRrWoKUzyvkfibFRJDXWqGKn_hu6bH55-tJAf4Nemttz326O9IfiKI4537dHbcDRHz3ymBbLVU5LRlOi7w8T1UtKu9cw5J-w4fyvh9ARRYH7QJHDdxoI6l6Pyk/s327/tab10.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="327" data-original-width="290" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUkLG0-EjrK7rRrWoKUzyvkfibFRJDXWqGKn_hu6bH55-tJAf4Nemttz326O9IfiKI4537dHbcDRHz3ymBbLVU5LRlOi7w8T1UtKu9cw5J-w4fyvh9ARRYH7QJHDdxoI6l6Pyk/s16000/tab10.png" /></a></div><p>By definition from the documentation<br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijB_Yj08n51S1sGv6DCH1RcJrb3WUCVpKV8uVkjlln4b6yIGStSUUx_l3F2nwbJEZ9ZDLgEqO20TIpFVT1mgDv60RGUKrEjCEjKc5UApTUWnr9cgaKxOfzTj6vGWkFY1alnfdl/s738/Immagine+2021-07-20+173954.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="359" data-original-width="738" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijB_Yj08n51S1sGv6DCH1RcJrb3WUCVpKV8uVkjlln4b6yIGStSUUx_l3F2nwbJEZ9ZDLgEqO20TIpFVT1mgDv60RGUKrEjCEjKc5UApTUWnr9cgaKxOfzTj6vGWkFY1alnfdl/s16000/Immagine+2021-07-20+173954.png" /></a></div><br /><p></p><p>Because I partitioned by "machine_id", in this simple case, the set are all rows from 01//Jan to 15/Jan.<br /></p><p>The first value NOT NULL is the 01/Jan/20. The second value NOT NULL is the 04/Jan/20. The 3th, the 10/Jan/20 and the 4th, 13/Jan/20.</p><p>All others rows are NULLS.</p><p>Using the IGNORE NULLS option (always by definition):</p><p></p><p><span style="font-family: courier;">with<br />-----------<br />fix_1_ as (<br />-----------<br />SELECT<br /> elaboration_day,<br /> machine_id,<br /> lag(location_type) over (partition by machine_id order by elaboration_day) next_r,<br /> case<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) is null<br /> then elaboration_day<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type<br /> then elaboration_day<br /> else null<br /> end fix,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))<br />-------------<br />, fix_2_ as (<br />-------------<br />SELECT<br /> elaboration_day,<br /> LAST_VALUE (fix) IGNORE NULLS<br /> OVER (PARTITION BY machine_id ORDER BY elaboration_day <br /> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,<br /> location_type<br />FROM<br /> fix_1_)<br />----------------------<br />select<br /> *<br /> from fix_2_<br /> order by elaboration_day;</span></p><p><span style="font-family: courier;"> </span></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAPMC-lE6F90xHN6DDirevX6coXPcnL-w9pGTC4fyYC2HoyKpJUT7TCmvowKsvXV1tHijvVnYaVVEy74lzz-RbP164AyhW-1B8wAXqZJRo7QrL7-gPZLYpIJyMnCtrIBEmGPf7/s977/Immagine+2021-07-20+174823.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="352" data-original-width="977" height="334" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAPMC-lE6F90xHN6DDirevX6coXPcnL-w9pGTC4fyYC2HoyKpJUT7TCmvowKsvXV1tHijvVnYaVVEy74lzz-RbP164AyhW-1B8wAXqZJRo7QrL7-gPZLYpIJyMnCtrIBEmGPf7/w927-h334/Immagine+2021-07-20+174823.png" width="927" /></a></div><p></p><p>The key here is the "<span style="font-family: courier;">ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</span>" option. In fact</p><ul style="text-align: left;"><li>On 2/Jan/20 the not null LAST_VALUE from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 02/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 01/Jan/20. On 03/Jan, the LAST_VALUE NOT NULL from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 03/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 01/Jan/20.</li><li>On 04/Jan, the not null LAST_VALUE from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 04/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 04/Jan/20. On 05/Jan, the LAST_VALUE NOT NULL from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 05/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 04/Jan/20.</li><li>On 10/Jan, the not null LAST_VALUE from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 10/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 10/Jan/20. On 11/Jan, the LAST_VALUE NOT NULL from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 11/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 10/Jan/20.</li><li> On 13/Jan, the not null LAST_VALUE from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 13/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 13/Jan/20. On 15/Jan, the LAST_VALUE NOT NULL from 01/Jan (<span style="font-family: courier;">UNBOUNDED PRECEDING</span>) to 15/Jan (<span style="font-family: courier;">CURRENT ROW</span>), is 13/Jan/20 <br /></li></ul><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_48.html"><< Part05<br /></a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-22108335476710177922021-07-20T15:59:00.006+02:002021-07-24T11:16:33.110+02:00Analytic Functions preserve the "PARTITION BY" clause (Part 5)<p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_20.html"><< Part04</a><span> </span><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_21.html"><span><span></span></span>Part06 >></a> <br /></p><p>Now, using the LAST_VALUE analytic function, I can "fill the blank"</p><p><span style="font-family: courier;">with<br />-----------<br />fix_1_ as (<br />-----------<br />SELECT<br /> elaboration_day,<br /> machine_id,<br /> lag(location_type) over (partition by machine_id order by elaboration_day) next_r,<br /> case<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) is null<br /> then elaboration_day<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type<br /> then elaboration_day<br /> else null<br /> end fix,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))<br />-------------<br />, fix_2_ as (<br />-------------<br />SELECT<br /> elaboration_day,<br /> machine_id,<br /> LAST_VALUE (fix) IGNORE NULLS<br /> OVER (PARTITION BY machine_id ORDER BY elaboration_day <br /> <span> </span>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> fix_1_)<br />select * from fix_2_<br /> order by elaboration_day;</span></p><p> <br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_QDqRkvIfuPSK4zuZDcgqwRuD7WPESYcbVtFUIzbWKdBthv9g8Juw6RKI_7f7NCDqkNGCRQ2Pt9jVLrmpc9UCPMwfZmx_aodlUlRADSQFIDELJZ8RDb-Obvxi1FLcAiCJyyMf/s635/tab8.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="397" data-original-width="635" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_QDqRkvIfuPSK4zuZDcgqwRuD7WPESYcbVtFUIzbWKdBthv9g8Juw6RKI_7f7NCDqkNGCRQ2Pt9jVLrmpc9UCPMwfZmx_aodlUlRADSQFIDELJZ8RDb-Obvxi1FLcAiCJyyMf/s16000/tab8.png" /></a></div><p></p><p> </p><p>I'll explain why the LAST_VALUE "fill the blank" in a different post.</p><p>At this point I can separate the 4 interval. To show this I use again COUNT and ROW_NUMBER analytic function</p><p><span style="font-family: courier;">with<br />-----------<br />fix_1_ as (<br />-----------<br />SELECT<br /> elaboration_day,<br /> machine_id,<br /> lag(location_type) over (partition by machine_id order by elaboration_day) next_r,<br /> case<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) is null<br /> then elaboration_day<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type<br /> then elaboration_day<br /> else null<br /> end fix,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))<br />-------------<br />, fix_2_ as (<br />-------------<br />SELECT<br /> elaboration_day,<br /> machine_id,<br /><b> LAST_VALUE (fix) IGNORE NULLS<br /> OVER (PARTITION BY machine_id ORDER BY elaboration_day <br /> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,</b><br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> fix_1_)<br />----------------------<br />select<br /> elaboration_day,<br /> machine_id,<br /><b> count(location_type) over (partition by machine_id, location_type, fix) nrd,<br /> row_number() over (partition by machine_id, location_type, fix order by elaboration_day) rn,</b><br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />from fix_2_<br /> order by elaboration_day;</span><br /></p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGYnzdCxdoNz1fUhiYZp8fStF6FhlWnOmOUTG4e1kkOTHUHi5XAdAAPrFZb8NePNaCqGxGfJWNNMIgVwd6dSm1VVZZYtGKSsW9FhG4qCb85xyHOLu3v4JBsDgISNQ27aE_B3b1/s641/tab9.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="393" data-original-width="641" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGYnzdCxdoNz1fUhiYZp8fStF6FhlWnOmOUTG4e1kkOTHUHi5XAdAAPrFZb8NePNaCqGxGfJWNNMIgVwd6dSm1VVZZYtGKSsW9FhG4qCb85xyHOLu3v4JBsDgISNQ27aE_B3b1/s16000/tab9.png" /></a></div><p></p><p> </p><p>How you can see, now COUNT function (NRD column) count only rows in it a local range and not over all. The same is true for ROW_NUMBER function (NR column) that restart each time LOCATION_TYPE change.</p><p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_20.html"><< Part04</a><span> </span><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_21.html"><span><span></span></span>Part06 >></a></p><p> </p>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-22748554542779696122021-07-20T14:23:00.005+02:002021-07-24T11:16:01.735+02:00Analytic Functions preserve the "PARTITION BY" clause (Part 4)<p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_51.html"><< Part03</a><span> </span><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_48.html"><span><span></span></span>Part05>></a> <br /></p><p>Now the question is: is it exists a way to not preserve the partitioning?</p><p>There are a lot of solutions to this question. In the next posts I'm going to show you the one I used.</p><p>First of all, I have to evaluate the previous row: if the location change, then there is a "point of change" as in the following picture</p><p><span style="font-family: courier;">SELECT<br /> elaboration_day,<br /> <b>lag(location_type) over (partition by machine_id order by elaboration_day) next_r</b>,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')<br /> order by elaboration_day;</span></p><p><span style="font-family: courier;"> </span><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHxiQEF72B1H-US_tX7daDoPeBBJBxcjDJFPYpK8k0PSFfr1c4OHW9FIX81aIUoJAcxcOG5kxc_8K1w-ckazUnhagvpF3_jTCB0LiuZFZTNfFJlxTL6x3FpxHB-AKUWRJUVTUw/s532/tab6.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="389" data-original-width="532" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHxiQEF72B1H-US_tX7daDoPeBBJBxcjDJFPYpK8k0PSFfr1c4OHW9FIX81aIUoJAcxcOG5kxc_8K1w-ckazUnhagvpF3_jTCB0LiuZFZTNfFJlxTL6x3FpxHB-AKUWRJUVTUw/s16000/tab6.png" /></a></div><p></p><p><br /> So, there are 2 different conditions to evaluate the "point of change":</p><ol style="text-align: left;"><li>WHEN next "location_type" is NULL (true only for the first row)<br /></li><li>WHEN next "location_type" != actual "location_type"<br /></li></ol><p> IF one of these 2 conditions are true THEN I use the elaboration_day, ELSE NULL</p><p><span style="font-family: courier;"> SELECT<br /> elaboration_day,<br /> lag(location_type) over (partition by machine_id order by elaboration_day) next_r,<br /> <b>case<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) is null<br /> then elaboration_day<br /> when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type<br /> then elaboration_day<br /> else null<br /> end fix,</b><br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')<br /> order by elaboration_day;</span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSb3ftS-JdMeLRLspwIXbhEQDlldU22GTyD7kplihXA8Xonxg_3EHOnrJHXkJQsTH_ryk_Otkqiz_5BO3RGUo-FY-Mw-64ZqZ-41HGSWtlasaI2Dax1FbbbxaBQ4AC22y3Mko0/s602/tab7.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="401" data-original-width="602" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSb3ftS-JdMeLRLspwIXbhEQDlldU22GTyD7kplihXA8Xonxg_3EHOnrJHXkJQsTH_ryk_Otkqiz_5BO3RGUo-FY-Mw-64ZqZ-41HGSWtlasaI2Dax1FbbbxaBQ4AC22y3Mko0/s16000/tab7.png" /></a></div> <p></p><p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_51.html"><< Part03</a><span> </span><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_48.html"><span><span></span></span>Part05 >></a></p><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-29642589060815567542021-07-19T23:32:00.013+02:002021-07-24T11:15:29.873+02:00Analytic Functions preserve the "PARTITION BY" clause (Part 3)<p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_19.html"><< Part02</a> <a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_20.html">Part04 >> </a><br /></p><p>In order to understand why, I use the ROW_NUMBER analytic function</p><p><span style="font-family: courier;">SELECT<br /> elaboration_day,<br /> row_number() over (partition by machine_id, location_type order by elaboration_day) rn,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')<br /> order by elaboration_day;<br /></span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ8NleIneEhIzXujtx3Wi4i1hvwOTMLrxZomkvGdVP0EuQfymuf8gsUlq4wJzWLiyjwWgy85v95LZa6kJP3TDIsDbQ29jKIu_Orm3SiGwCDDodXx0U3RNToPrWDE9cjjLsRJHH/s478/tab5.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="347" data-original-width="478" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ8NleIneEhIzXujtx3Wi4i1hvwOTMLrxZomkvGdVP0EuQfymuf8gsUlq4wJzWLiyjwWgy85v95LZa6kJP3TDIsDbQ29jKIu_Orm3SiGwCDDodXx0U3RNToPrWDE9cjjLsRJHH/s16000/tab5.png" /></a></div><br /><p></p><p>How you can see, the ROW_NUMBER, <i><u>preserve </u></i>the PARTITIONING and numerate in ordered way the days inside the partition.</p><p>It could be strange, but it's the (right) way the analytic function works.</p><p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_19.html"><< Part02</a> <a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_20.html">Part04 >> </a></p><p> <br /></p>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-66227655759270054972021-07-19T23:14:00.011+02:002021-07-24T11:15:12.372+02:00Analytic Functions preserve the "PARTITION BY" clause (Part 2)<p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition.html"><< Part01</a> <a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_51.html">Part03 >></a> <br /></p><p>Question. I want to know how many continues days the slot-machine was in specific location<br /></p><p>Start using minimum columns: <br /></p><p><span style="font-family: courier;">SELECT<br /> elaboration_day,<br /> location_type,<br /> commercial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')<br /> order by elaboration_day;</span></p><p></p><p></p><p></p><p></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguRoyJfAFSmvKxiVHTVopnqFLdVWXXSE1CEvjWRYrPMhg8333c5wBBAZlurt2SgIOwYhYEZ1gYxwbVMd5zmHtcNfbs1qQkXNDZ0B_JsZCjhzytXMccIYyCJb3_njZKVxXeCQnx/s440/tab3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="341" data-original-width="440" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguRoyJfAFSmvKxiVHTVopnqFLdVWXXSE1CEvjWRYrPMhg8333c5wBBAZlurt2SgIOwYhYEZ1gYxwbVMd5zmHtcNfbs1qQkXNDZ0B_JsZCjhzytXMccIYyCJb3_njZKVxXeCQnx/s16000/tab3.png" /></a></div><br /><p> If I count the days by LOCATION_TYPE:</p><p><span style="font-family: courier;">SELECT<br /> elaboration_day,<br /> <b>count(location_type) over (partition by machine_id, location_type) nrd</b>,<br /> location_type,<br /> comme+rcial_id,<br /> warehouse_id<br />FROM<br /> awp_machine<br /> where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')<br /> order by elaboration_day;</span></p><p>I don't have 3 + 3 + 6 + 3 days. Instead I have 6 + 9 days as shown in the following picture<br /></p><p></p><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIhzLCdeLX7L7POVG6wUgW6VBpl_mONecscHMmTmiCACjuAwEDMEmfyCVXqISKhWZ4PNjWc2I_CKpRQmhekZHHSPoKlJlehp397WyTHlsUvChvRzqi4fXlLO-I9wVw1NAIkXno/s488/tab3a.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="347" data-original-width="488" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIhzLCdeLX7L7POVG6wUgW6VBpl_mONecscHMmTmiCACjuAwEDMEmfyCVXqISKhWZ4PNjWc2I_CKpRQmhekZHHSPoKlJlehp397WyTHlsUvChvRzqi4fXlLO-I9wVw1NAIkXno/s16000/tab3a.png" /></a></div><p></p><p>This is because the COUNT analytic function, count the days where the machine was during the interval I use (15 days in this case: 01/Jan - 15/Jan)<br /></p><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSVSn276AkDfb8xSAbYQvFcbUpNX2BjIk6kuTUMlRP9nC3nlpQER7VJYWi0SXcDhuLBkOfLnALFP21kyAmGbyrCENj8IWsFlJQlrmzFo6aPI42MMj2KhorO4EbiAcCDnXPvycc/s485/tab4.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="336" data-original-width="485" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSVSn276AkDfb8xSAbYQvFcbUpNX2BjIk6kuTUMlRP9nC3nlpQER7VJYWi0SXcDhuLBkOfLnALFP21kyAmGbyrCENj8IWsFlJQlrmzFo6aPI42MMj2KhorO4EbiAcCDnXPvycc/s16000/tab4.png" /></a> </div><div class="separator" style="clear: both; text-align: center;"> </div><div class="separator" style="clear: both; margin-left: 40px; text-align: left;"><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition.html"><< Part01</a> <a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_51.html">Part03 >></a></div>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-75704892892215391122021-07-19T14:29:00.031+02:002021-07-24T11:14:32.952+02:00Analytic Functions preserve the "PARTITION BY" clause (Part 1)<p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_19.html">Part02 >> </a><br /></p><p>The Analytic Functions are very powerful and I use them every day. What I learned is that some behaviors could seems strange or bugs, but it's the normal way how to the Analytic Function works.</p><p>This is the scenario: a slot-machine has a counter that identify how much money is inserted until now. For example if on 03/Jan at 23:59:59, the counter is 10, then 10€ are inserted into machine until 03/Jan.</p><p>On 04/Jan at 23:59:59, the counter is 11. This means that 11€ are inserted into machine until 04/Jan.</p><p>On 04/Jan the machine gained 1€ (11 - 10).</p><p>In order to know how much money a slot machine has gained in one day I need the counters of the machine, day by day.</p><p>A slot machine can be in a Bar or in Warehouse if it needs repair. If it is in the Bar then people can play, otherwise, if the machine is in Warehouse, it isn't used and gain 0 (zero). Anyway, because the machine is "ACTIVE" (potentially it could gain money), by default we assign 560€ as "FLAT" value (it is a convention used by the govern). <br /></p><p>So, the CNTTOTIN is the counter IN and represent the money the people insert into machine. At the same way, there is the CNTTOTOT and represent the money the people win (coin OUT from the machine).</p><p>When FLAT is used, it means that the machine is not read, then the previous CNTTOTIN is used as counter. In this way, the TAXABLE report the FLAT value, meanwhile the WINNINGS is set to 0 (zero).<br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7nBnpH-82FNcKXDWsWRO5qrvQuIs7DJ5CqpX9UGB2M2loYtln9tGztlxNRViM4D3hHp52xf8IlDq6lVyOPjLtUABW2FknnsUU-6Wy0ijLeUFPEhVO_DCx4s9sQk-Eskpcffx0/s1174/tab01.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="325" data-original-width="1174" height="255" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7nBnpH-82FNcKXDWsWRO5qrvQuIs7DJ5CqpX9UGB2M2loYtln9tGztlxNRViM4D3hHp52xf8IlDq6lVyOPjLtUABW2FknnsUU-6Wy0ijLeUFPEhVO_DCx4s9sQk-Eskpcffx0/w918-h255/tab01.png" width="918" /> </a></div><div class="separator" style="clear: both; text-align: center;"> </div>
<div>
<table align="center" border="1px solid black">
<tbody><tr>
<th>COLUMN NAME</th>
<th>MEANING</th>
</tr>
<tr>
<td>ELABORATION_DAY</td>
<td>Day of elaboration</td>
</tr>
<tr>
<td>MACHINE_ID</td>
<td>Machine identifier</td>
</tr>
<tr>
<td>CNTTOTIN</td>
<td>IN counter</td>
</tr>
<tr>
<td>CNTTOTOT</td>
<td>OUT counter</td>
</tr>
<tr>
<td>COMMERCIAL_ID</td>
<td>Location identifier</td>
</tr>
<tr>
<td>WAREHOUSE_ID</td>
<td>Warehouse identifier</td>
</tr>
<tr>
<td>WINNINGS</td>
<td>How much money the people wins in a day (2)<br /></td>
</tr>
<tr>
<td>TAXABLE</td>
<td>How much money the machine gained in a day (flat value if "active" and in warehouse) (1)(2)<br /></td>
</tr>
<tr>
<td>LOCATION_TYPE</td>
<td>CME for COMMERCIAL LOCATION; WRH for WAREHOUSE</td>
</tr>
<tr>
<td>CONTRACT_BASE_ID</td>
<td>Contract identifier if the machine is in WRH, NULL if in CME</td>
</tr>
<tr>
<td>CONTRACT_COMMERCIAL_ID</td>
<td>Contract identifier if the machine is in CME, NULL if in WRH</td>
</tr>
<tr>
<td>OPERATION_TYPE</td>
<td>"Read" if the cpunter is detected, "Flat" otherwise</td>
</tr>
</tbody></table>
(1) The column name reflect the fact that from the value, we don't have yet substracted taxe</div><div>(2) The values are in €cent</div><div> </div><div>Here, how to create the table and popolate it. <br /></div><div> </div>
<div>
<span style="font-family: courier;">CREATE TABLE AWP_MACHINE (</span></div><div><span style="font-family: courier;"><span> </span>ELABORATION_DAY<span> </span><span></span><span></span><span> </span><span> </span>DATE, </span></div><div><span style="font-family: courier;"><span> </span>MACHINE_ID <span> </span><span> <span> </span><span> </span></span><span></span><span></span>NUMBER(15),</span></div><div><span style="font-family: courier;"><span style="font-family: courier;"><span> </span>CNTTOTOT<span> </span><span> </span><span> </span><span> </span><span> </span>NUMBER(15),</span> <br /></span></div><div><span style="font-family: courier;"><span> </span>CNTTOTIN<span> </span><span> </span><span> </span><span> </span><span> </span>NUMBER(15), <br /></span></div><div><span style="font-family: courier;"><span> </span>COMMERCIAL_I<span>D<span> <span> </span><span> </span></span></span><span></span>NUMBER(15),</span></div><div><span style="font-family: courier;"><span> </span>WAREHOUSE_ID<span> </span><span> </span><span></span><span></span><span> </span><span> </span>NUMBER(15),</span></div><div><span style="font-family: courier;"><span> </span>WINNINGS<span> </span></span><span style="font-family: courier;"> </span><span style="font-family: courier;"> <span> </span><span> </span></span><span style="font-family: courier;"></span><span style="font-family: courier;">NUMBER(15),</span></div><div><span style="font-family: courier;"><span> </span>TAXABLE<span> </span></span><span style="font-family: courier;"> </span><span style="font-family: courier;"> </span><span style="font-family: courier;"><span> </span><span> </span>NUMBER(15),</span></div><div><span style="font-family: courier;"><span> </span>LOCATION_TYPE<span> <span> </span><span> </span></span></span><span style="font-family: courier;">VARCHAR2(3 BYTE),</span></div><div><span style="font-family: courier;"><span> </span>CONTRACT_BASE_ID </span><span style="font-family: courier;"><span> </span><span> </span>NUMBER(15),</span></div><div><span style="font-family: courier;"><span> </span>CONTRACT_COMMERCIAL_ID<span><span><span> </span></span></span>NUMBER(15),</span></div><div><span style="font-family: courier;"><span> </span>OPERATION_TYPE<span> </span></span><span style="font-family: courier;"><span></span></span><span style="font-family: courier;"><span> </span><span> </span>VARCHAR2(8 BYTE) );</span></div><p><br /><span style="font-family: courier;">Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('01/01/2020', 'DD/MM/YYYY'), 1205770, 73535500, 55272700, 542703, NULL,<br /> 200, 1000, 'CME', 2656069, 'Read');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('02/01/2020', 'DD/MM/YYYY'), 1205770, 73537900, 55274700, 542703, NULL,<br /> 2000, 2400, 'CME', 2656069, 'Read');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('03/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, 542703, NULL,<br /> 10300, 9100, 'CME', 2656069, 'Read');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('04/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('05/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('06/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('07/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('08/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('09/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('10/01/2020', 'DD/MM/YYYY'), 1205770, 73556400, 55288700, 542703, NULL,<br /> 3700, 9400, 'CME', 2656069, 'Read');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('11/01/2020', 'DD/MM/YYYY'), 1205770, 73558800, 55289100, 542703, NULL,<br /> 400, 2400, 'CME', 2656069, 'Read');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('12/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, 542703, NULL,<br /> 1000, 1400, 'CME', 2656069, 'Read');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('13/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('14/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('15/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,<br /> 0, 56000, 'WRH', 2669766, 'Flat');<br />COMMIT;<br /></span></p><p>In order to calculate TAXABLE and WINNINGS for the first day, you need an extra insert</p><p><span style="font-family: courier;">Insert into AWP_MACHINE<br /> (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,<br /> WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)<br /> Values<br /> (TO_DATE('31/12/2019', 'DD/MM/YYYY'), 1205770, 73534500, 55272500, 542703, NULL,<br /> NULL, NULL, 'CME', 2656069, 'Read');</span></p><p><a href="https://orasal.blogspot.com/2021/07/analytic-functions-preserve-partition_19.html">Part02 >> </a></p>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com1tag:blogger.com,1999:blog-27589819.post-4502631498043534452018-08-16T13:33:00.001+02:002018-08-16T13:35:05.684+02:00Lock Chains 02: GV$SESSIONIn my previous <a href="https://orasal.blogspot.com/2017/12/lock-chains-01-vsession.html">post</a>, I showed the chain of the locks using a query for just an instance. Recently <a href="http://kcdatabase.com/">Kaley Crum</a>, an active member of the <a href="https://www.orapub.com/">Orapub</a> community, modified my statement so now you can use it in RAC configuration.<br />
<br />
I only modified a little, the output:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">col BLOCKING_SESSION for a10</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">col LAST_LOCKED_SESSION for a10</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">col PATH_EVENT for a120</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">set lines 210</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">set pages 99</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">with</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> snap as (</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> inst_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sid</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , blocking_instance</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , blocking_session</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sql_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , prev_sql_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , event</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , state</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> gv$session)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , CONNECT_BY_ISCYCLE cycle</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , CONNECT_BY_ROOT inst_id ||':'||</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> CONNECT_BY_ROOT sid blocking_session</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , inst_id ||':'||</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> SID last_locked_session</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , SUBSTR(SYS_CONNECT_BY_PATH(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> DECODE(state,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> 'WAITING', sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ,5) path_event</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from snap </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">where</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> level > 1</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">connect by</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> nocycle prior sid = blocking_session</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> and prior inst_id = blocking_instance;</span><br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHhmoD8Mcns16FWztLMdqVfiIPyDIItn8M-1AezLb08bzGDO57mH-Oyk1NGxwv5DUd-czRKBUr3-kxRzCHTx26r-yTmwfs6rCRIBLE2PBY90xZ38_W-4Q9c0BlRuHdA33-_IR7/s1600/lockPath.PNG" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="216" data-original-width="1036" height="133" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHhmoD8Mcns16FWztLMdqVfiIPyDIItn8M-1AezLb08bzGDO57mH-Oyk1NGxwv5DUd-czRKBUr3-kxRzCHTx26r-yTmwfs6rCRIBLE2PBY90xZ38_W-4Q9c0BlRuHdA33-_IR7/s640/lockPath.PNG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Picture 01 - Click to enlarge</td></tr>
</tbody></table>
<br />
Note: How to read the output<br />
<br />
1. The column BLOCKING_SESSION and LAST_LOCKED_SESSION are composed of 2 members separated by the colon. For example, on the first line, 1:1313 mean that on the instance 1 the session 1313 is the head of the lock chain where the last element is the session 609 on the instance 1.<br />
<br />
2. In the PATH_EVENT column, the "session" and "instance" are related by an "at". For example the on the last line, 1377@1 mean that the session 1377 on the instance 2 is locking the session 1441 on the instance 2Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-41520313743849424952018-04-03T13:29:00.004+02:002018-04-03T13:30:55.385+02:00Info about SCANIn my company, we have more than 1000 databases and sometimes is very hard to manage all of them.<br />
<br />
So I created some scripts that help me to manage some things.<br />
<br />
One of this is the information on the SCANs<br />
<br />
The SCAN port, the SCAN name and the network interface where the SCAN listeners are configured are different. So, in order to check the information of the SCAN configuration, I wrote following <a href="https://www.dropbox.com/s/fayjvtzrl4eea9j/scaninfo.sh?dl=0">script</a><br />
<br />
The output is something like this<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">###################################################################</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">########################### SCAN info #############################</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">###################################################################</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Scan Name : nodecl-scan</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Scan Port : 1555</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Dedined on : Network: 1</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Subnet IPv4 : 10.10.226.0/255.255.254.0</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Network Card : </span><span style="font-family: "courier new" , "courier" , monospace;">eth0</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">LISTENER_SCAN1: 10.10.227.38/</span><span style="font-family: "courier new" , "courier" , monospace;">eth0</span><span style="font-family: "courier new" , "courier" , monospace;">:2 (ONLINE on node01)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">LISTENER_SCAN2: 10.10.227.39/</span><span style="font-family: "courier new" , "courier" , monospace;">eth0</span><span style="font-family: "courier new" , "courier" , monospace;">:3 (ONLINE on node02)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">LISTENER_SCAN3: 10.10.227.40/</span><span style="font-family: "courier new" , "courier" , monospace;">eth0</span><span style="font-family: "courier new" , "courier" , monospace;">:3 (ONLINE on node01)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">###################################################################</span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
As you can see, the output shows also the interface where the listener is bound. In this case, on the node01, the listeners are on eth0:2 and eth0:3 with 10.10.267.38 and 10.10.267.40, respectively. On the node02 instead, the SCAN listener 2, with IP address 10.10.267.39, is on eth0:3</div>
<div>
<br /></div>
<div>
In case of the crs is down, the script returns the following error</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">>>>>> CRS is not working correctly <<<<<</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">>>>>> Check the crs status <<<<<</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">CRS-6750: unable to get the active version CRS-6752: Active version query failed</span></div>
</div>
<div>
<br /></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-56281201298428242012017-12-20T11:28:00.003+01:002017-12-20T11:33:13.706+01:00Active Session History: Updating the X$ASH, part 03<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash.html">Part 01</a><br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_18.html">Part 02</a><br />
<br />
Here I try to show you what happens and why Oracle updates the V$ASH view. Please read the <a href="https://drive.google.com/file/d/178B2cLEcj3GqOTRcyVs79rpQcT2UwSOU/view">pdf </a>by Graham Wood, for more details.<br />
<br />
What I analyze is the session "session_id=1732" with "session_serial#=31065" saw in the <a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_18.html">previous post</a>.<br />
<br />
This is what happens<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgaGtuVfhRQoqAJ80t3y-AR-lkK2CDOpfSy8j7AJEnCQcODk5am27FWnWRtQyK2SYrKBkhL_MVCDOqZPJhHPQ8mDN2XMym_6Rj8VznfBWJrAb2eap9AaUJNwIwFOVLESG0doj7/s1600/p01.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="119" data-original-width="1101" height="68" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgaGtuVfhRQoqAJ80t3y-AR-lkK2CDOpfSy8j7AJEnCQcODk5am27FWnWRtQyK2SYrKBkhL_MVCDOqZPJhHPQ8mDN2XMym_6Rj8VznfBWJrAb2eap9AaUJNwIwFOVLESG0doj7/s640/p01.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for enlarge</td></tr>
</tbody></table>
<br />
The session 1732 (serial# 31065) is sampled 3 times: at sample_id 50243482, 50243483, 50243484. In the last sample_id, there is a change of state of the session. In fact, querying the V$ACTIVE_SESSION_HISTORY, you see<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtOQqo9mRJm3SwOK7KZdKn43VwRGJ46epV6sx3LgAiih7bhuLF-GmZy1Wo4VSNlq_KA7Ljcu6Q9TkDqOeO8_clonjNY9d8Pm6cv6ftb2M5KUZwKUV86BXhD-_1qgI4csznX3ZV/s1600/p02.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="81" data-original-width="821" height="62" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtOQqo9mRJm3SwOK7KZdKn43VwRGJ46epV6sx3LgAiih7bhuLF-GmZy1Wo4VSNlq_KA7Ljcu6Q9TkDqOeO8_clonjNY9d8Pm6cv6ftb2M5KUZwKUV86BXhD-_1qgI4csznX3ZV/s640/p02.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for enlarge</td></tr>
</tbody></table>
<br />
But what happens behind the lines is something like this. Pay attention that my conclusions are based on my experiment<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmToQi2gluMalMzuFgiiE-nxEy4lmHs1HSJnqMPI1NIZMGOEjZu3IW-DLWYQIoWceg_ax2m5Ozc59ayWa7aywrlSUb12mHmjpH6yvNaU1byyry7DP0LtCowLkqj3brEbVTwSTJ/s1600/a01.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="114" data-original-width="1069" height="68" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmToQi2gluMalMzuFgiiE-nxEy4lmHs1HSJnqMPI1NIZMGOEjZu3IW-DLWYQIoWceg_ax2m5Ozc59ayWa7aywrlSUb12mHmjpH6yvNaU1byyry7DP0LtCowLkqj3brEbVTwSTJ/s640/a01.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for enlarge</td></tr>
</tbody></table>
<br />
<h4>
(1) SAMPLE_ID 50243482/SAMPLE_TIME 15-DEC-17 02:50:40.054</h4>
<div>
At the 15/12/2017 14:50:40.838429, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:40.054</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5pb6PvyBalX8mdOatkrXByFkr2CudApm92XtCxY82C6Shq-NOIRAxjTyIS42tJB9nBknyXreGjgAHhBpQvPNPzwGWoVZpqcnwiO6mD9Q2qcBKNIiYwPMWPlpZQfEa9c8C83d-/s1600/Diapositiva1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="549" data-original-width="941" height="372" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5pb6PvyBalX8mdOatkrXByFkr2CudApm92XtCxY82C6Shq-NOIRAxjTyIS42tJB9nBknyXreGjgAHhBpQvPNPzwGWoVZpqcnwiO6mD9Q2qcBKNIiYwPMWPlpZQfEa9c8C83d-/s640/Diapositiva1.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<h4>
(2) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:42.064</h4>
<div>
At the 15/12/2017 14:50:42.016313, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:41.064</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit0CRsg0Kv8u31eLapVcRAY7h3kCfrL79jzAF1fEnjTgQJEQIb12Yi-scJtednpxeoT1ewNVpQcefsKnu4uFHMsrnlauD4Om_wBz_ghAKJVKtgqRFhT1oGn8MA6bX7s9A86drn/s1600/Diapositiva2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="586" data-original-width="945" height="396" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit0CRsg0Kv8u31eLapVcRAY7h3kCfrL79jzAF1fEnjTgQJEQIb12Yi-scJtednpxeoT1ewNVpQcefsKnu4uFHMsrnlauD4Om_wBz_ghAKJVKtgqRFhT1oGn8MA6bX7s9A86drn/s640/Diapositiva2.JPG" width="640" /></a></div>
<br />
<h4>
(3) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064</h4>
At the 15/12/2017 14:50:43.192604, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:42.064<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim2_LLSZlfyYRL9f0374a63lfCyGotNGCwT9UtyiPyipvemHosIJ7Q-aDEFGQlK1SFcNzmaKcKzR_RxoUk6Ov6ZVhvldDWolEMr25pQhUKPv5AP1lR6BOPobzXs0-lrmypbTMf/s1600/Nuovo+Presentazione+di+Microsoft+PowerPoint.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="624" data-original-width="957" height="416" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim2_LLSZlfyYRL9f0374a63lfCyGotNGCwT9UtyiPyipvemHosIJ7Q-aDEFGQlK1SFcNzmaKcKzR_RxoUk6Ov6ZVhvldDWolEMr25pQhUKPv5AP1lR6BOPobzXs0-lrmypbTMf/s640/Nuovo+Presentazione+di+Microsoft+PowerPoint.jpg" width="640" /></a></div>
<br />
Note that, at this time, the session 1732 is waiting, but Oracle doesn't know yet how match time the session will wait<br />
<br />
<h4>
(4) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064 (the same of the previous one)</h4>
<div>
At the 15/12/2017 14:50:44.372309 (sample_id 50243485), the last row is updated in X$ASH with SAMPLE_TIME, 14:50:42.064</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<br /></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivA4I9BHT4slW2utFBMmPwsRHeN8_qAhg6_1fJGNZc5drpL3alt5nV445GXvtHhYPPdH4O7rXE5HAe-FPIc4gt1hoY3U_klyuKUVmRRurtFnCx3PyMupK6I7BMzLanlDuRvaHE/s1600/Nuovo+Presentazione+di+Microsoft+PowerPoint2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="664" data-original-width="960" height="442" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivA4I9BHT4slW2utFBMmPwsRHeN8_qAhg6_1fJGNZc5drpL3alt5nV445GXvtHhYPPdH4O7rXE5HAe-FPIc4gt1hoY3U_klyuKUVmRRurtFnCx3PyMupK6I7BMzLanlDuRvaHE/s640/Nuovo+Presentazione+di+Microsoft+PowerPoint2.jpg" width="640" /></a></div>
<div>
<br /></div>
<div>
At sample_id 50243485, the session 1732 finish it works and Oracle know how much time the session waited. So he can update the state of the wait on X$ASH</div>
<div>
<br /></div>
<div>
Here I left the previous row, just to show what happening.</div>
<div>
<br /></div>
<div>
<h4>
(5) The final result</h4>
</div>
<div>
What you see finally, is a consistent state of the session</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWBylt7XiSyc3EA_8-DqaIBLKtcOmH_4TiHE9Mkk1o28cOxSz4VspQ0TiKRyAZe8fbJXPSRoWBdFMeUeRtxf_K69BGuPS4FsmaMaBqvPFh_iWiu0kAgqB1ozviF3YqhCtCtS0E/s1600/Diapositiva3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="626" data-original-width="960" height="416" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWBylt7XiSyc3EA_8-DqaIBLKtcOmH_4TiHE9Mkk1o28cOxSz4VspQ0TiKRyAZe8fbJXPSRoWBdFMeUeRtxf_K69BGuPS4FsmaMaBqvPFh_iWiu0kAgqB1ozviF3YqhCtCtS0E/s640/Diapositiva3.JPG" width="640" /></a></div>
<div>
<br /></div>
<div>
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash.html">Part 01</a><br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_18.html">Part 02</a></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com1tag:blogger.com,1999:blog-27589819.post-41587115786233688122017-12-18T15:12:00.002+01:002017-12-20T11:32:21.073+01:00Active Session History: Updating the X$ASH, part 02<br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash.html">Part01</a><br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_20.html">Part03</a><br />
<br />
Starting from previous <a href="https://drive.google.com/open?id=1Ez4ojuBsSZhy58oj9M_K_f4acfjuSw0M">output</a>, running the following query<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">set lines 160</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">col SAMPLE_TIME for a30</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">set pages 99</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_serial#</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , count(*)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_time </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> my_active_session_history </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">group by </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_serial#</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">order by </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> session_id </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<br />
you can aggregate the result. The output is <a href="https://drive.google.com/open?id=1AhPzg-w0X4dS6mRLSn8n1SkiSxBUqBI_">here</a>.<br />
<br />
I get an extract of this output to try to explain what happens.<br />
<br />
What <a href="https://drive.google.com/open?id=1Gkz5EjNGWpj-JR-75TQcwANKDBgIOrUM">my anonymous PL/SQL</a> (see the <a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash.html">previous post</a>) does, is to polling the V$SESSION_ACTIVE_SESSION every second and save the output in MY_ACTIVE_SESSION_HISTORY table.<br />
<br />
Because each time, the script read the same rows plus one (the V$ASH is renewed each second), for the same sessions, you see a lot of rows for each loop<br />
<br />
Consider, for example, the session 1732/serial 31065.<br />
<br />
select<br />
sample_id<br />
, session_id<br />
, session_serial#<br />
, time_waited<br />
, count(*)<br />
, sample_time<br />
from<br />
my_active_session_history<br />
where session_id=1732 and session_serial#=31065<br />
group by<br />
sample_id<br />
, session_id<br />
, session_serial#<br />
, time_waited<br />
, sample_time<br />
order by<br />
session_id<br />
, sample_id<br />
/<br />
<div>
<br /></div>
<div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxJqtGapklpPcH5g4m3yHoVzmR71QZ_YoX7R4bKpyehvcx4a_NmpSPH9JwMh_2MJFYDyvBxjks3-Rc9DtuRnMagulcbQ-WZ63__iwFOcpD9z1AIyquHXqybaQhuw4BwkfDyP5x/s1600/details01.jpg" style="margin-left: auto; margin-right: auto; text-align: center;"><img border="0" data-original-height="765" data-original-width="1194" height="410" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxJqtGapklpPcH5g4m3yHoVzmR71QZ_YoX7R4bKpyehvcx4a_NmpSPH9JwMh_2MJFYDyvBxjks3-Rc9DtuRnMagulcbQ-WZ63__iwFOcpD9z1AIyquHXqybaQhuw4BwkfDyP5x/s640/details01.jpg" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for enlarge</td></tr>
</tbody></table>
</div>
<br />
It appears 50 times for the snap_id 50243482, 49 times for the snap_id 50243483 and 48 times for snap_id 50243484 (see note).<br />
<br />
What happens to the session 1732, where for the sample_id=50243484 you see two distinct rows?<br />
<br />
The following statement has <a href="https://drive.google.com/open?id=1rKkrHk5NazcxmuUyuD2ALEUGESXu_vs7">this</a> output (it is just the detail of the session 1732):<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> row_number() over (partition by sample_id order by sample_id</span><span style="font-family: "courier new" , "courier" , monospace;">, inserting_time) rn</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , </span><span style="font-family: "courier new" , "courier" , monospace;">inserting_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , wait_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_state</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , event</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> my_active_session_history </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">where </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> session_id=1732 and session_serial#=31065</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">order by </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sample_id, 1</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<br />
Because I'm interested just in the first rows of each change of "sample_id" and/or change of the "state" inside the same sample_id, I want to see only this changes<br />
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">with </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> mash as </span><span style="font-family: "courier new" , "courier" , monospace;">(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> inserting_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , wait_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , event</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_state</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , </span><span style="font-family: "courier new" , "courier" , monospace;">row_number() over (partition by sample_id, time_waited, session_state, event order by inserting_time) rn</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> my_active_session_history</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> where </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> session_id=1732 and session_serial#=3106</span><span style="font-family: "courier new" , "courier" , monospace;">5</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , inserting_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_state</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , event</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> mash</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">where </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> rn =1</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">order by </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , inserting_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<div>
<br /></div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7g9kGOp6lslVSBR0ktjZvfoLcxZLnK7vo-hvQfAX8Hf72DBhYkGebICLxFQaTdsmBwAfZz0Z7SFaLKVDDYDde5wT6EA5jtR9N_t8HQlg_m9M2e_Dap5-cPF5retGRqdy_dvZv/s1600/a01.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="114" data-original-width="1069" height="68" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7g9kGOp6lslVSBR0ktjZvfoLcxZLnK7vo-hvQfAX8Hf72DBhYkGebICLxFQaTdsmBwAfZz0Z7SFaLKVDDYDde5wT6EA5jtR9N_t8HQlg_m9M2e_Dap5-cPF5retGRqdy_dvZv/s640/a01.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for enlarge</td></tr>
</tbody></table>
<br />
What happens here?<br />
<br />
<br />
<ol>
<li>At sample_id =50243482 (14:50:40) the sid 1732 is working (it's ON CPU). This means that TIME_WAITED is 0 (zero)</li>
<li>One second after (14:50:41), sample_id=50243483, the sid 1732 is still working and TIME_WAITED is still 0 (zero)</li>
<li>One second after (14:50:42), sample_id=50243484, the sid 1732 wait on direct path read event, but the TIME_WAITED column is not updated. So while the SESSION_STATE/EVENT columns report the correct state of the session, the TIME_WAITED, not yet (inserting_time=15-DEC-17 02.50.<span style="color: red;">43.192604</span> PM). Because of this Oracle update on the same SNAP_ID (50243484) this row with the correct value of TIME_WAITED column (inserting_time=15-DEC-17 02.50.<span style="color: red;">44.372309 PM</span>).</li>
</ol>
<br />
In the next session, I try to explain this "update" in a visual way<br />
<h3>
Note</h3>
Is it correct that each next sample_id, have one less? When my PL/SQL script inserts a row the first time, it does at 15-DEC-17 02.<span style="color: red;">50.<span style="color: red;">40</span></span>.833420 PM (sample_id=50243482). The second time, it inserts at 15-DEC-17 02.<span style="color: red;">50.<span style="color: red;">42</span></span>.016313 PM (sample_id=50243483): it inserts two seconds after the first time. The third time it inserts at 15-DEC-17 02.<span style="color: red;">50.<span style="color: red;">43</span></span>.192604.<br />
Anyway, the last record for all samples is the same: 15-DEC-17 02.<span style="color: red;">51.40</span>.488761 PM. Because all samples end at the same time (MX column in the next picture), each of them count one record less.<br />
<br />
You can run the following select that show you the min and max timestamp<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select * from (</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_serial#</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , inserting_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , max(inserting_time) over (partition by sample_id) mx</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , count(*) over (partition by sample_id) cnt</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , row_number() over (partition by sample_id order by inserting_time) rn_min</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , row_number() over (partition by sample_id order by inserting_time desc) rn_max</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> my_active_session_history</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> where </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> session_id=1732 and session_serial#=31065</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> order by</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_id</span><span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">where rn_min=1 or rn_max=1</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">/</span><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCumFSkNfy5dmLpy36WZfHx2qJPVOB7Y0jDikaHzYwxGi9sDSvIf_Jcu2nXoOyS9Nzc1HEH-xFRiCJgKWP0z4S5kEJDr9Yv9ojqModar0BnUYOEGsI0qt6w22tINjPVvH1l514/s1600/a02.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="144" data-original-width="1418" height="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCumFSkNfy5dmLpy36WZfHx2qJPVOB7Y0jDikaHzYwxGi9sDSvIf_Jcu2nXoOyS9Nzc1HEH-xFRiCJgKWP0z4S5kEJDr9Yv9ojqModar0BnUYOEGsI0qt6w22tINjPVvH1l514/s640/a02.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click for enlarge</td></tr>
</tbody></table>
<b>Please, pay attention. While the inserting_time is the instant when the row is inserted in the MY_ACTIVE_SESSION_HISTORY, <u>the sampling happens each second</u> (in the previous picture I put both SAMPLE_TIME and INSERTING_TIME)</b><br />
<div>
<br /></div>
<div>
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash.html">Part01</a><br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_20.html">Part03</a></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-4761676330781711312017-12-18T10:34:00.001+01:002017-12-20T11:29:58.246+01:00Active Session History: updating the X$ASH, part 01<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_18.html">Part 02</a><br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_20.html">Part 03</a><br />
<br />
I'm a proud member of <a href="http://www.orapub.com/">OraPub </a>community. Some days ago I was speaking with <a href="http://kcdatabase.com/">Kaley Crum</a>, another member of the OraPub community.<br />
<br />
The context was ASH (Active Session History).<br />
<br />
He told me that at some time, Oracle does an update of X$ASH in order to update the values of the TIME_WAITED column.<br />
<br />
I didn't trust him because my conviction was that Oracle makes just an insert in this X$.<br />
<br />
Well, I was wrong. Kaley right. Thanks to him to learn me something new.<br />
<br />
On this argument, what is TIME_WAITED column, and some trouble with it. Kayle wrote a <a href="http://kcdatabase.com/wait_time-vs-time_waited">post</a> himself.<br />
<br />
Also, and I recommend it to you, a couple of post by Craig Shallahamer. You can find them <a href="http://blog.orapub.com/20150827/what-is-the-oracle-ash-time-waited-column.html">here </a>and <a href="http://blog.orapub.com/20150909/How-To-Use-The-Oracle-ASH-time_waited-column.html">here</a>. And not forget the <a href="http://www.adellera.it/blog/2016/06/23/ash-math-of-time_waited-explained-with-pictures-and-simulation">one</a> wrote by Alberto Dell'Era.<br />
<br />
And don't miss the <a href="https://drive.google.com/open?id=178B2cLEcj3GqOTRcyVs79rpQcT2UwSOU">pdf </a>of Graham Wood, slide 35, where he shows the fallacies of the TIME_WAITED column in the V$ACTIVE_SESSION_HISTORY view. This series of posts are based on this slide nr 35.<br />
<br />
What I want to do is try to explayn myself the problem, but here, in Part 01, I want just write the test I made.<br />
<br />
<br />
1) Prepare the enviroment<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">create table my_active_session_history as select * from v$active_session_history where 1=2;<br /> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">alter table my_active_session_history add (inserting_time timestamp);</span><br />
<br />
<br />
2) Run the <a href="https://drive.google.com/open?id=1Gkz5EjNGWpj-JR-75TQcwANKDBgIOrUM">following </a>anonymous PL/SQL<br />
<br />
<br />
3) Run following statement, to report the result<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br />col INSERTING_TIME for a30<br />col SAMPLE_TIME for a30<br />set lines 120</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> inserting_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_time</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_serial#</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , event</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , session_state</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , time_waited<br />from</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> my_active_session_history<br />order by </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> session_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , sample_id</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , inserting_time;</span><br />
<br />
You can see the result of this last statement, <a href="https://drive.google.com/open?id=1Ez4ojuBsSZhy58oj9M_K_f4acfjuSw0M">here</a>.<br />
<br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_18.html">Part 02</a><br />
<a href="http://orasal.blogspot.it/2017/12/active-session-history-updating-xash_20.html">Part 03</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-49460933525588388832017-12-11T13:58:00.000+01:002017-12-11T14:14:09.032+01:00Lock Chains 01: V$SESSION<span style="font-family: "georgia" , "times new roman" , serif;">I always receive a call where a colleague tell me that a session of an application is slow. Really that session is not slow but locked by another one. </span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">Starting with 10g, Oracle introduced a new column in the V$SESSION performance view, that report the instance number and the session id of the blockers. In this way, the troubleshooting is more simple.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><br /></span> <span style="font-family: "georgia" , "times new roman" , serif;">Anyway, after you query the V$SESSION you have to scroll down the result set in order to match the blocking session with the locked session.</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">Because the relation between the blocker and locked sessions is of "hierarchical" type, I can use the hierarchical query (session 123 is blocking the session 234 and session 345. This last one also is blocking the session 999, for example) to try to picture this relation.</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">***** </span><br />
<span style="font-family: "georgia" , "times new roman" , serif;">***** <i>Please pay attention. The following statements are not RAC aware yet. So they work just on a single instance</i> *****</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;">*****</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><br /></span> <span style="font-family: "courier new" , "courier" , monospace;">col path_state for a30<br />col path_event for a160<br />col path_sid for a20 <br />col b_session for 999999<br />set lines 230<br />set pages 25 <br />with<br /> snap as (<br /> select<br /> sid<br /> , blocking_instance<br /> , blocking_session<br /> , sql_id<br /> , prev_sql_id<br /> , event<br /> , state<br /> from<br /> v$session)<br />select<br /> to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now<br /> , CONNECT_BY_ISCYCLE cycle<br /> , CONNECT_BY_ROOT sid b_session<br /> , SID l_session<br /> , SUBSTR(SYS_CONNECT_BY_PATH(<br /> DECODE(state,<br /> 'WAITING', sid||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,<br /> sid||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')<br /> ,5) path_event<br /> from<br /> snap<br />where <br /> level >1<br />connect by<br /> nocycle prior sid=blocking_session<br />/</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"></span><br />
<span style="font-family: "georgia" , "times new roman" , serif;">The result is like this</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyqP1m3ia6rovwyJuXfxq6b6x13WexxcDKFbLEppupI3qINSCmSeoqeYt4eiCMNT3O_2FZgOZUNgZlo6oz9AveKlPLDLcE3q6T6dW8915O5WVELIpdsNhkjkAXROpYhbVeMOw0/s1600/wait101.JPG" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="334" data-original-width="1359" height="156" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyqP1m3ia6rovwyJuXfxq6b6x13WexxcDKFbLEppupI3qINSCmSeoqeYt4eiCMNT3O_2FZgOZUNgZlo6oz9AveKlPLDLcE3q6T6dW8915O5WVELIpdsNhkjkAXROpYhbVeMOw0/s640/wait101.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Picture 01 - Click for enlarge</td></tr>
</tbody></table>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgj474-MivsjM9z53MzG3LRmZjPeE9oEuPeAZX1COcgqLfEjH5SPDbfvM5FxOUsWHfBgtEpWEQcJqe0YUzod-yC3uUiNo3N-NsyiLnN0t2GwHSQwKBrzSRFZPWRUE__F-GFjbbL/s1600/wait102.JPG" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="336" data-original-width="1494" height="142" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgj474-MivsjM9z53MzG3LRmZjPeE9oEuPeAZX1COcgqLfEjH5SPDbfvM5FxOUsWHfBgtEpWEQcJqe0YUzod-yC3uUiNo3N-NsyiLnN0t2GwHSQwKBrzSRFZPWRUE__F-GFjbbL/s640/wait102.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Picture 02 - Click for enlarge</td></tr>
</tbody></table>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<span style="font-family: "georgia" , "times new roman" , serif;">There are a lot of things that seem to be strange.</span><br />
<span style="font-family: "georgia" , "times new roman" , serif;"><br /></span>
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">##### Picture 01</span>
<br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">There are sessions that lock each other. They have the column "CYCLE" to 1. Both of them are waiting for "read by other sessions" event. This is strange because it seems to be a deadlock, but there isn't a trace file containing the "ORA-00060: deadlock detected while waiting for resource".</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">Other curious things are the "enq: TX - row lock contention". In green, I highlighted this. The session 2330 is waiting for the session 2330 that doing...nothing.</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;"> In blue, instead, you can see how a session waiting for "read by other session" depend from a session is reading the same block. </span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">##### Picture 02</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">Here you can see how some sessions (in red) are locked by a session that did...nothing (in orange).</span><br />
<br />
<span style="font-family: "georgia" , "times new roman" , serif;">In the next post, I'll show a similar statement that you can use on V$ACTIVE_SESSION_HISTOR.</span><br />
<br />
<h3>
<span style="font-family: "georgia" , "times new roman" , serif;">Note: How to read the output</span></h3>
<ol>
<li><span style="font-family: "georgia" , "times new roman" , serif;">The column CYCLE, tell you if you have a deadlock (like in picture 01). In this case, there will be a "1"</span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif;">B_SESSION and L_SESSION columns are the blocking session (the head) and the locked session (the tail), respectively</span><span style="font-family: "georgia" , "times new roman" , serif;"> </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif;">PATH_EVENT column, show you the lock path. It includes the Session ID, the SQL ID and the wait event if that session in waiting. Otherwise, the wait event is replaced by "ON CPU"</span><span style="font-family: "georgia" , "times new roman" , serif;"> </span></li>
<li><span style="font-family: "georgia" , "times new roman" , serif;">If the SQL_ID is NULL, then I try to use the PREV_SQL_ID. In this case a "(p)" will appear (for example "2sbqfhnjz6ybw(p)" in orange in the picture 02). Anyway there are some cases where also the previous SQL_ID is NULL. In this case there will be only the "(p)" without the SQL_ID (B_SESSION = 1401, in the picture 02, for example).</span></li>
</ol>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-22434099262569724232017-10-11T15:29:00.000+02:002017-10-11T16:01:21.726+02:00Using XML: Index<h2>
<span style="font-family: "gill sans";">Part One: Introduction to XML</span></h2>
<div>
<ul>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/08/using-xml-in-oracle-part-01.html" id="id_1f7b_e4a1_f11c_ec0d" target="_self">Introduction, Part 01</a>: What is this?</span></li>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/08/using-xml-introduction-part-02_28.html" id="id_ef04_c7c3_1d23_b667">Introduction, Part 02</a>: Anatomy of a document</span></li>
<li><span style="font-family: "gill sans";"><span style="-webkit-text-size-adjust: auto; background-color: rgba(255, 255, 255, 0);"><a href="http://orasal.blogspot.it/2017/08/in-this-post-i-continue-introduction-of.html" id="id_d75f_2983_450_2d0c">Introduction, Part 03</a></span>: Path</span></li>
</ul>
</div>
<h2>
<span style="font-family: "gill sans";">Part Two: From XML to rows and columns</span></h2>
<div>
<div style="line-height: normal; margin: 0px;">
</div>
<ul>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table.html" id="id_67b4_9fd4_c53a_1b96">Insert, Part 01</a>: Passing values</span></li>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table-part-22.html" id="id_d3c8_7e9c_3a3_e751">Insert, Part 02</a>: Insert via SQL*Plus</span></li>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/08/using-xml-06-select-part-12.html" id="id_9d56_2b74_d2b8_7b9c">Select, Part 01</a>: EXTRACT and EXTRACTVALUE</span></li>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/08/using-xml-07-select-part-23.html" id="id_63c2_9695_cc66_4d16">Select Part 02</a>: XMLQuery</span></li>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/09/using-xml-08-select-part-33a.html" id="id_c1b7_38d8_440d_9ead">Select, Part 03a</a>: XMLTable</span></li>
<li><span style="font-family: "gill sans";"><span style="-webkit-text-size-adjust: auto; background-color: rgba(255, 255, 255, 0);"><a href="http://orasal.blogspot.it/2017/09/using-xml-09-select-part-33b.html" id="id_480c_8d02_29ad_6a4">Select, Part 03b</a></span>: XMLTable (continued)</span></li>
<li><span style="font-family: "gill sans";"><a href="http://orasal.blogspot.it/2017/09/using-xml-10-comparing-functions.html" id="id_3fad_71d9_85e4_1172">Recap</a></span></li>
</ul>
</div>
<h2>
<span style="font-family: "gill sans";">Part Three: From rows and columns to XML</span></h2>
<ul>
<li><a href="http://orasal.blogspot.it/2017/09/using-xml-12-xmlelement.html">XMLElement</a></li>
<li><a href="http://orasal.blogspot.it/2017/09/using-xml-12-xmlattributes.html">XMLAttributes</a></li>
<li><a href="http://orasal.blogspot.it/2017/09/using-xml-13-xmlforest.html">XMLForest</a></li>
<li><a href="http://orasal.blogspot.it/2017/09/using-xml-14-xmlconcat.html">XMLConcat</a></li>
<li><a href="http://orasal.blogspot.it/2017/09/using-xml-15-xmlagg.html">XMLAgg</a></li>
<li><a href="http://orasal.blogspot.it/2017/10/using-xml-17-xmlcolattval.html">XMLColAttVal</a></li>
<li><a href="http://orasal.blogspot.it/2017/10/using-xml-18-dbmsxmlgen.html">DBMS_XMLGEN.GETXMLTYPE</a></li>
</ul>
<br />
<span style="font-family: "gill sans";"></span><br />
<div id="_blog_touch_end_br_for_reorient">
<span style="font-family: "gill sans";"><br /></span></div>
<div id="_blog_touch_end_br_for_reorient">
<span style="font-family: "gill sans";"><br /></span></div>
<div id="_blog_touch_end_br_for_reorient">
<br /></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-35697140141985578852017-10-11T15:28:00.002+02:002017-10-11T15:56:06.279+02:00Using XML 17: DBMS_XMLGENI started this series on XML because of a query wrote by Connor McDonald. Please read my previous <a href="http://orasal.blogspot.it/2017/09/using-xml-10-comparing-functions.html">post</a>.<br />
<br />
Now there is just a last post where I want to talk about DBMS_XMLGEN.GETXMLTYPE.<br />
<br />
First of all the definition of DBMS_XMLGEN package[1].<br />
<br />
From [1]: <i>The DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB. This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run on the database.</i><br />
<br />
You can find the list of procedure and functions of DBMS_XMLGEN package in [2]<br />
<div>
<br /></div>
The GETCMLTYPE function definition is taken from [3]: <i>Gets the XML document and returns it as XMLType</i><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAlRSXpBfZWvUQdIllIlS-aeAcnLULBUkoFF7U3HFMwNYaw7H1QBGwJPZVcKqOC_cjCh3ARKRMq40sK7wRoFvfFlic_EVFp4ClyBagQYYIT18w8r153E0PgxPYn8O5sJ95w12q/s1600/getxmltype.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="138" data-original-width="670" height="130" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAlRSXpBfZWvUQdIllIlS-aeAcnLULBUkoFF7U3HFMwNYaw7H1QBGwJPZVcKqOC_cjCh3ARKRMq40sK7wRoFvfFlic_EVFp4ClyBagQYYIT18w8r153E0PgxPYn8O5sJ95w12q/s640/getxmltype.JPG" width="640" /></a></div>
<br />
As you can see, the first argument is a sqlQuery. So, in order to try a test, I just run a simple query.<br />
<br />
First I a run the main statement and check the result<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3-X0nnsTxGR4Xnp99oOOvlwchPtxFzYXqpYvI9kKNQWzOYlvbS4ORxPIDsmWW_RLKlvsg6ZdqZ-j7_tozJio_iWvHJtR7L4yPtAglpDLWgdz2QuZgNIiKy5WeDU7HFQRHleSh/s1600/getxmltype02.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="154" data-original-width="648" height="152" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3-X0nnsTxGR4Xnp99oOOvlwchPtxFzYXqpYvI9kKNQWzOYlvbS4ORxPIDsmWW_RLKlvsg6ZdqZ-j7_tozJio_iWvHJtR7L4yPtAglpDLWgdz2QuZgNIiKy5WeDU7HFQRHleSh/s640/getxmltype02.JPG" width="640" /></a></div>
<br />
At this point, I run a really simple statement with DBMS_XMLGEN.GETXMLTYPE using the previous select.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXY0CgIOPFLT_OCXIkuXOhpUu5ai5_quJmsE6bTCMMVcO7fyb-9rG1RWh48QAmCtkSAmopiWG9kcQ6xytAT7cL6hIIDcmCe7ZozngX0D26KInoiC3DXEYDlEcBo3Plip1bZ8c8/s1600/getxmltype03.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="478" data-original-width="725" height="420" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXY0CgIOPFLT_OCXIkuXOhpUu5ai5_quJmsE6bTCMMVcO7fyb-9rG1RWh48QAmCtkSAmopiWG9kcQ6xytAT7cL6hIIDcmCe7ZozngX0D26KInoiC3DXEYDlEcBo3Plip1bZ8c8/s640/getxmltype03.JPG" width="640" /></a></div>
<br />
So, GETXMLTYPE transforms the resultset of a select in an XML. The root node is "ROWSET" while the children are "ROW". Inside this children, there are other children: one of each column specified in the inner select.<br />
<br />
Now I run the part of XML from my previous <a href="http://orasal.blogspot.it/2017/09/using-xml-10-comparing-functions.html">post</a> (I just modify owner a table name because I run the statement on a different database):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyWlA66rLmC_ribCMPnV0AHrVw5kx3jRykRlC7JJVM6TEe6k6AmEr7-pjfJlK7p5Vh_eg-t6IT6QEL94as0L98W1OsvV5E22F1lw7pxymwy16SZeXcA86rB8nYMB13Fhh4rbMl/s1600/getxmltype04.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="883" data-original-width="654" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyWlA66rLmC_ribCMPnV0AHrVw5kx3jRykRlC7JJVM6TEe6k6AmEr7-pjfJlK7p5Vh_eg-t6IT6QEL94as0L98W1OsvV5E22F1lw7pxymwy16SZeXcA86rB8nYMB13Fhh4rbMl/s640/getxmltype04.JPG" width="474" /></a></div>
<br />
At this point, you have all the instruments to correctly understand the Connor McDonald's <a href="https://www.youtube.com/watch?v=yKHQQXKdfOM&t=82s">select</a>.<br />
<br />
<h3>
Reference</h3>
[1] https://docs.oracle.com/cloud/latest/db121/ARPLS/d_xmlgen.htm#ARPLS374<br />
[2] <a href="https://docs.oracle.com/cloud/latest/db121/ARPLS/d_xmlgen.htm#ARPLS69848">Summary of DBMS_XMLGEN Subprograms</a><br />
[3] <a href="https://docs.oracle.com/cloud/latest/db121/ARPLS/d_xmlgen.htm#ARPLS69858">GETXMLTYPE Functions</a><br />
<br />
<a href="http://orasal.blogspot.it/2017/10/using-xml-17-xmlcolattval.html">Part16</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-48116659085741985252017-10-11T10:12:00.002+02:002017-10-11T15:55:27.940+02:00Using XML 16: XMLColAttValFrom [1]: <i>Oracle SQL function XMLColAttVal generates a forest of XML column elements containing the values of the arguments passed in.</i><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><span style="margin-left: auto; margin-right: auto;"><a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-F643284F-A6AF-47A9-AF2C-58EF7E88C87F__I1032066"><img border="0" data-original-height="119" data-original-width="604" height="126" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBrGiXJXk7kGp-gH-lXhdsxNRgTbhKtTAX1roqM5FjAzvu5yPjgzSN7s87uMJiBhxBTFf7bHD-Ce3iPuNm6C8KyXIobuIUGcN-0eAhExrrPOR3ZfZ8V2yJJ5dXF26UYeCrayla/s640/xmlcollatt.JPG" width="640" /></a></span></td></tr>
<tr><td class="tr-caption"><a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-F643284F-A6AF-47A9-AF2C-58EF7E88C87F__I1032066"><br /></a></td></tr>
</tbody></table>
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select XMLElement("User",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLAttributes(USER_ID as "User ID"),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLColAttVal(USERNAME, ORACLE_MAINTAINED as "Is_Ora_Owned", CREATED)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ) user_details</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8cLVkN-jRRF_OtFmZAbzuLB0XrZZOysqnSnOxPLZrcDvjFx4d30kNjftuAHgX65_AghyphenhyphenkvFlhpZ-UXvrz9zMm_dKdhjR41Hcg0Fl1-Ld4VYtdtXG7kDLvtK_4ty5zL0Ss40ea/s1600/xmlPict2.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="243" data-original-width="1314" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8cLVkN-jRRF_OtFmZAbzuLB0XrZZOysqnSnOxPLZrcDvjFx4d30kNjftuAHgX65_AghyphenhyphenkvFlhpZ-UXvrz9zMm_dKdhjR41Hcg0Fl1-Ld4VYtdtXG7kDLvtK_4ty5zL0Ss40ea/s640/xmlPict2.jpg" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
<h3>
References</h3>
<pre class="oac_no_warn" dir="ltr" style="background-color: white; color: #222222; font-size: 11.6188px;"></pre>
<pre class="oac_no_warn" dir="ltr" style="background-color: white; color: #222222; font-size: 11.6188px;"></pre>
[1] <a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-F643284F-A6AF-47A9-AF2C-58EF7E88C87F">https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-F643284F-A6AF-47A9-AF2C-58EF7E88C87F</a><br />
<br />
<a href="http://orasal.blogspot.it/2017/09/using-xml-15-xmlagg.html">Part15</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/10/using-xml-18-dbmsxmlgen.html">Part17</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-10251756025668237712017-09-25T13:52:00.000+02:002017-10-11T15:50:48.001+02:00Using XML 15: XMLAggFrom [1]: <i>You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements — it is an aggregate function.</i><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-971232BA-B52E-44CB-86DA-E182AF7E2ACB__I1032735"><img border="0" data-original-height="54" data-original-width="442" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFHxw6bPXFEWywXw5fokJ-T863wUQxSs6LddU3tE9i5p0zLaDtghK8W9RTQGIPWjVIGJe-weMzz6RXd0nUn7SrRDj46XVWW0Sj9BQG0jCFQ-GurvfQUYUtHYadZK8JFYqtTOIO/s640/XMLAgg.JPG" width="640" /></a></div>
<br />
<br />
<i>Numeric literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead, numeric literals are interpreted as any other literals.</i><br />
<br />
I want just try this select<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select USERNAME, XMLElement("Schemas",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLAgg(XMLElement("User", username),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("Id", USER_ID),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("OracleOwn ", ORACLE_MAINTAINED)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> )</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ) xml_schemas</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMejNd_KXjwR5WEX2BgZ7tZmnrC1sSTI9ADXy33tW__ANmR4jkCMx8cZyLFCKm39Cj1tDdNUz1WuenYaduBoZGXkBFbAxORvp4ysllNVFWrK6Mxk4Azfbta4haerlG9wodI2tQ/s1600/xml15.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="210" data-original-width="681" height="196" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMejNd_KXjwR5WEX2BgZ7tZmnrC1sSTI9ADXy33tW__ANmR4jkCMx8cZyLFCKm39Cj1tDdNUz1WuenYaduBoZGXkBFbAxORvp4ysllNVFWrK6Mxk4Azfbta4haerlG9wodI2tQ/s640/xml15.JPG" width="640" /></a></div>
<br />
Ok. It's failed. This is because I have to choose only one argument<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select USERNAME, XMLElement("Schemas",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLAgg(XMLElement("User", username)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ) xml_schemas</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhas6yCRrzhzP6tNZ8OXL5xP3_QraKY0es3A0PRnEInkcxtwpss5z8RTnWitCkNSFYfTiS08EpCU2jTq0RLtRhP05VwLS8ohI_VBoBLMS7ntqazxo60Y6JDzBhy6vUSMqjalCAG/s1600/xmlPict.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="166" data-original-width="527" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhas6yCRrzhzP6tNZ8OXL5xP3_QraKY0es3A0PRnEInkcxtwpss5z8RTnWitCkNSFYfTiS08EpCU2jTq0RLtRhP05VwLS8ohI_VBoBLMS7ntqazxo60Y6JDzBhy6vUSMqjalCAG/s640/xmlPict.jpg" width="640" /></a></div>
<br />
So I have modify the the statement<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select XMLElement("Schemas",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLAgg(XMLElement("User", username)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ) xml_schemas</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuiHYo2_ldGB0OcZU7gKHXCA7PvAD3CeIK-2QUxBwhwBj7f4ht2K9rfaxskK-4rNUFPRVapH6x8BcKNq5CgxnHiLP1xYlFXKFrFHYpPSKgnAHrc3Xa-UGWoTzv9Ds7q1yRiJ7q/s1600/xml16.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="198" data-original-width="1600" height="78" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuiHYo2_ldGB0OcZU7gKHXCA7PvAD3CeIK-2QUxBwhwBj7f4ht2K9rfaxskK-4rNUFPRVapH6x8BcKNq5CgxnHiLP1xYlFXKFrFHYpPSKgnAHrc3Xa-UGWoTzv9Ds7q1yRiJ7q/s640/xml16.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
<h3>
Update</h3>
2017/Oct/11 - Modified the statement because I wrote it badly<br />
<br />
<br />
<h3>
Reference</h3>
[1] <a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-971232BA-B52E-44CB-86DA-E182AF7E2ACB">https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-971232BA-B52E-44CB-86DA-E182AF7E2ACB</a><br />
<br />
<a href="http://orasal.blogspot.it/2017/09/using-xml-14-xmlconcat.html">Part14</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/10/using-xml-17-xmlcolattval.html">Part16</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-10378095808015360782017-09-25T10:37:00.001+02:002017-10-11T15:50:01.688+02:00Using XML 14: XMLConcatFrom [1]: <i>You use SQL/XML standard function XMLConcat to construct an XML fragment by concatenating multiple XMLType instances.</i><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-3806853D-2AAA-4D2D-AD98-3E0941F7BE17__I1032783"><img border="0" data-original-height="61" data-original-width="337" height="114" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhFZqaMgHea4UY7ZHr15BqDEPJ2Vpj4l_-nPfg3dSsnHrzumSoK25Yj2QJEDLJGlhRcF31YQZFcmQMhaSCcTOfrsYaAUAxol11CmDtGVECROvuoA5HACsNGpSOd7Y4PRlqrlOZ/s640/XMLConcat.JPG" width="640" /></a></div>
<br />
<br />
Following an example:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select USERNAME, XMLConcat(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("User", username),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),</span><br />
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("Id", USER_ID),</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLElement("OracleOwn ", ORACLE_MAINTAINED)</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">) xml_concat</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span></div>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_uylRRLaGJkFpU7r4sCX6X6HPreJcKnfKdJlmkZk9uW4FMNJ-wHD4jAGQ88OzfjKjUYWLmcN0aHCkYI3xC3ZHrK617sbwJNTnvy4JeqJvSjIjlP7IeZdULo_nnVp_W9B8gupj/s1600/xml14.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="255" data-original-width="948" height="172" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_uylRRLaGJkFpU7r4sCX6X6HPreJcKnfKdJlmkZk9uW4FMNJ-wHD4jAGQ88OzfjKjUYWLmcN0aHCkYI3xC3ZHrK617sbwJNTnvy4JeqJvSjIjlP7IeZdULo_nnVp_W9B8gupj/s640/xml14.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
Pay attention to the "blank" in the "OracleOnw" string: it is mantained.<br />
<br />
<h3>
References</h3>
[1] <a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-3806853D-2AAA-4D2D-AD98-3E0941F7BE17">https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-3806853D-2AAA-4D2D-AD98-3E0941F7BE17</a><br />
<br />
<a href="http://orasal.blogspot.it/2017/09/using-xml-13-xmlforest.html">Part13</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-15-xmlagg.html">Part15</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-9633586240068929312017-09-25T10:10:00.000+02:002017-10-11T15:45:35.199+02:00Using XML 13: XMLForestYou use SQL/XML standard function XMLForest to construct a forest of XML elements.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-D9666485-0383-4AF6-B352-B9A90AE1DCFB__I1030830"><img border="0" data-original-height="110" data-original-width="586" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-pQvuLN49OWWnMpfIg7EYffuIDzoFT-Hf9kRE4rfSanEXl2YUVv_I9fCo8aBBlQk-k-bu2G7fhlHNnskCmg_CIGf-FZvG4or-RFFwFU51Oct_MU6EUErTj-XA-EsRVD6BLluq/s640/XMLForest.JPG" width="640" /></a></div>
<br />
From [1]: <i>Each of the value expressions (value_expr in Figure 8-3) is converted to XML format, and, optionally, identifier "alias" is used as the attribute identifier. For an object type or collection, the AS clause is required. For other types, the AS clause is optiona</i>l<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiN_RfO2m03unFW_QJM76JtUNX6VlMZkEdeyLLZv5zlymfofquJabQBLziRdB18c9NM-Ldi3Mj-EQy0NCr7nRtJ2rBgu4tfVYSdW-ubRS1HxeL27ErIvm40Oqfb6Bmw-WzmTwl/s1600/xml07.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="216" data-original-width="870" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiN_RfO2m03unFW_QJM76JtUNX6VlMZkEdeyLLZv5zlymfofquJabQBLziRdB18c9NM-Ldi3Mj-EQy0NCr7nRtJ2rBgu4tfVYSdW-ubRS1HxeL27ErIvm40Oqfb6Bmw-WzmTwl/s640/xml07.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
Well, I need to rename the element:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select username,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLForest(username, to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss') when, </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> USER_ID, ORACLE_MAINTENED) XML_forest</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsa7x4CNqZks_yXzBw9QFGd5uAkDJZIdcbMXqZ5dKITnPkavYIVME-cMFeiEoLCZDKyvXRwM8xL7t3V2Jybrg5KubWZe4aGnoOaOBXEGWliTMhhndRgQvkLm-KJDxMBcBgrreE/s1600/xml08.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="369" data-original-width="914" height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsa7x4CNqZks_yXzBw9QFGd5uAkDJZIdcbMXqZ5dKITnPkavYIVME-cMFeiEoLCZDKyvXRwM8xL7t3V2Jybrg5KubWZe4aGnoOaOBXEGWliTMhhndRgQvkLm-KJDxMBcBgrreE/s640/xml08.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
I also can indent the functions. In following example I use XMLElement and XMLForest<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgU0osI_oGSGY0rU4Ve0PqzQ5sJYSMqD0_ionN0VsBiwDTRtQuQQGOu6WNZoYmGZq7yRRf_xBfL1IfP3mtvF0LU13iBXS8mOVM2b5L7Wdgbv_nP-HhpFC-rgAX1JVxWmKaSTjhg/s1600/xml11.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="277" data-original-width="933" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgU0osI_oGSGY0rU4Ve0PqzQ5sJYSMqD0_ionN0VsBiwDTRtQuQQGOu6WNZoYmGZq7yRRf_xBfL1IfP3mtvF0LU13iBXS8mOVM2b5L7Wdgbv_nP-HhpFC-rgAX1JVxWmKaSTjhg/s640/xml11.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
Just let me reformat the output changing the column size<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-jT2tjXpfHV_KrJTBe1KmE_VKWVowyKhxWqlZK_Na-M-CIJ4NjKxfnH-z62nAYXkk0XD6jB1KtLo6TQo56ZsDGSz2kCLYG2Mvyag0dILhCd1sMZpgcxqgpPHMlQzdG6RftWSO/s1600/xml12.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="214" data-original-width="1381" height="99" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-jT2tjXpfHV_KrJTBe1KmE_VKWVowyKhxWqlZK_Na-M-CIJ4NjKxfnH-z62nAYXkk0XD6jB1KtLo6TQo56ZsDGSz2kCLYG2Mvyag0dILhCd1sMZpgcxqgpPHMlQzdG6RftWSO/s640/xml12.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
and I can indent the functions<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmnexXQfM5Tjn8O2jdr_PAz8sF3Uc8NR8LqgCS5Zyrmq8auCkamDPa9EMixcjSi4cRhDV_nMTfVlgnElyN6i6JS_OznDpL39yQawVPmMeLkfQZFlWFS_sx6vB7ay9NttGZe3vC/s1600/xml13.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="240" data-original-width="1233" height="124" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmnexXQfM5Tjn8O2jdr_PAz8sF3Uc8NR8LqgCS5Zyrmq8auCkamDPa9EMixcjSi4cRhDV_nMTfVlgnElyN6i6JS_OznDpL39yQawVPmMeLkfQZFlWFS_sx6vB7ay9NttGZe3vC/s640/xml13.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<h3>
References</h3>
[1] <a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-D9666485-0383-4AF6-B352-B9A90AE1DCFB">https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-D9666485-0383-4AF6-B352-B9A90AE1DCFB</a><br />
<br />
<a href="http://orasal.blogspot.it/2017/09/using-xml-12-xmlattributes.html">Part12</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-14-xmlconcat.html">Part14</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-4014579832030168072017-09-25T09:57:00.003+02:002017-10-11T15:44:52.540+02:00Using XML 12: XMLAttributesFrom [1]: <i>SQL/XML standard function XMLAttributes can be used together with XMLElement, to specify attributes for the generated elements. The first argument to function XMLElement defines an identifier that names the root XML element to be created. The root-element identifier argument can be defined using a literal identifier. If an attribute value expression evaluates to NULL, then no corresponding attribute is created. The data type of an attribute value expression cannot be an object type or a collection.</i><br />
<div>
<br /></div>
<div>
This is the syntax:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77__BABBDEJF"><img border="0" data-original-height="307" data-original-width="491" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJ3KLATrEyS7wuMIEQQwuzWAxsDJZwdNZVD-l9W0GeX2kqVKGXbDNifrJOllAUY9nqLL1clpfEYfTYRcpM3J6O3CiqYqmt2Hc-5-fvcSOfTdvgZIwn_O4tmOjrKIqJ1YJPnzVW/s640/XMLAttributes.JPG" width="640" /></a></div>
<br />
I run following statement<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select username, XMLElement("Schema",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> XMLAttributes(username as "User",</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss') as "When"</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> USER_ID as "Id"</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ORACLE_MANTAINED as "OracleOwn")</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> ) xml_user_details</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from all_users fetch first 3 rows only</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhg2tGTKByQnLZpGT_BbNEV28J9bavkR2BBVxXWbkxiZqGe6Dzj665rY9o2AdMOVglPqmOJ_qQl7D0BSh50LNpkqCbMgHXWeM7olQl2m3-hW1sugM_j4JjKHI7uTaaB9J9db0V/s1600/xml_B01.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="270" data-original-width="838" height="206" id="id_1eed_1772_c37d_6583" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhg2tGTKByQnLZpGT_BbNEV28J9bavkR2BBVxXWbkxiZqGe6Dzj665rY9o2AdMOVglPqmOJ_qQl7D0BSh50LNpkqCbMgHXWeM7olQl2m3-hW1sugM_j4JjKHI7uTaaB9J9db0V/s640/xml_B01.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
As you can see, the XML in yellow rectangle, is not complete. I have to format the column, changing the value of long<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjy1kdtfu21hJb8RxTICKfwWY_fgtZ0NtVm_Aqn-NT1FAUTtnXKa6HoMzEuKeNzwfSoyjXvmpDXUuH4HJS61VpsVbTiT7LmvSkQeZXrSuWPu9smEQIIa-GOUpZkLp1a3ueWxab-/s1600/xml_B02.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="290" data-original-width="917" height="202" id="id_976d_e907_167c_2c20" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjy1kdtfu21hJb8RxTICKfwWY_fgtZ0NtVm_Aqn-NT1FAUTtnXKa6HoMzEuKeNzwfSoyjXvmpDXUuH4HJS61VpsVbTiT7LmvSkQeZXrSuWPu9smEQIIa-GOUpZkLp1a3ueWxab-/s640/xml_B02.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<h3>
References</h3>
[1] <a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77">https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77</a><br />
<br />
<a href="http://orasal.blogspot.it/2017/09/using-xml-12-xmlelement.html">Part11</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-13-xmlforest.html">Part13</a></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-68117009801579399422017-09-19T09:24:00.000+02:002017-10-11T15:57:30.551+02:00Using XML 11: XMLElementFrom [1]. <i>The SQL/XML standard function XMLElement constructs XML elements from relational data. It takes as arguments an element name, an optional collection of attributes for the element, and zero or more additional arguments that make up the element content. It returns an XMLType instance.<br />
<br />
The first argument to function XMLElement defines an identifier that names the root XML element to be created. The root-element identifier argument can be defined and, if it is defined, the identifier must not be NULL or else an error is raised.</i><br />
<br />
This is the syntax:<br />
<div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77__I1030781"><img border="0" data-original-height="229" data-original-width="665" height="220" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvFr0sjBPIBqpAT6Kb1hWWpR6yfKXsYmMZg6tzweB4GcE0Q61lv9vg287nDUZKZLnTpgjnRMsBRL5ZtTwAh_W_LSGme_tkW5q57AsNqmvTjc-bxyYHnp1YcJrp-0ybWxU71Aro/s640/XMLElement.JPG" width="640" /></a><span id="goog_1630374148"></span><span id="goog_1630374149"></span><a href="https://www.blogger.com/"></a></div>
<br />
I want to do a very simple test. I use the ALL_USERS view, in order to the run the first select.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihW0oP7RwAHtWJG7TDfTL2WxrTHu0YSdLSxlxXoDmW-d15OBT1ZflRy7d6nhkondjp-PFuvdLeOFFrFtWp44Ut3bZnjqJXmvwx2TOlKOgNbx9dEmb_3P_G3l6tXx-3jV0GaLRj/s1600/xml01.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="257" data-original-width="907" height="180" id="id_9fb9_4267_ea1c_ebf1" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihW0oP7RwAHtWJG7TDfTL2WxrTHu0YSdLSxlxXoDmW-d15OBT1ZflRy7d6nhkondjp-PFuvdLeOFFrFtWp44Ut3bZnjqJXmvwx2TOlKOgNbx9dEmb_3P_G3l6tXx-3jV0GaLRj/s640/xml01.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Let's reformat the output<br />
<div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpGJEy3I2j0V0t3PiB2xz42NuXP8rOvjxjyvX_aCq2UMJ2654qrmoRPrpz2h03oq6iw7lLcyjKdpmdM77oS5gP_UsNGnfdtfh0OnAKoHJ2flFcE-iqXQxEIHv7wP7PJYhd1Fjl/s1600/xml02.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="200" data-original-width="989" height="128" id="id_728d_ce4a_7b0d_d371" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpGJEy3I2j0V0t3PiB2xz42NuXP8rOvjxjyvX_aCq2UMJ2654qrmoRPrpz2h03oq6iw7lLcyjKdpmdM77oS5gP_UsNGnfdtfh0OnAKoHJ2flFcE-iqXQxEIHv7wP7PJYhd1Fjl/s640/xml02.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Ok. How you can see, the XMLElement, transform the column in a XML. Let's see more on the ALL_USERS view:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzjie6UuFackEq698RMiw7XEvifAH1c6WynByjawHZfyZ2Nfp-bgLxU64lw0V9icRaZsT82Hg-yucxMWlAcZOVt46TrqPH_eivP_1Bnl5BaLLqMlQLStZPrav4zDkN0csR3mEf/s1600/xml03.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="155" data-original-width="650" height="152" id="id_582e_1667_87d4_7948" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzjie6UuFackEq698RMiw7XEvifAH1c6WynByjawHZfyZ2Nfp-bgLxU64lw0V9icRaZsT82Hg-yucxMWlAcZOVt46TrqPH_eivP_1Bnl5BaLLqMlQLStZPrav4zDkN0csR3mEf/s640/xml03.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
I add a second column in my select (green and red).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOPUCWuijajMw_pxNw4AKaYahQrISXCv3HDYdLYaGOSG8b__GvocCBvl-zAM1kpcBy1I1vFQQjNwUD5LxO9bDApR0TD6vo6GrKVh6wBrMt_2WlofZ8yQHGmpwysAwA5saMFQV3/s1600/xml04.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="284" data-original-width="840" height="216" id="id_84c3_5f4c_67e0_7be7" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOPUCWuijajMw_pxNw4AKaYahQrISXCv3HDYdLYaGOSG8b__GvocCBvl-zAM1kpcBy1I1vFQQjNwUD5LxO9bDApR0TD6vo6GrKVh6wBrMt_2WlofZ8yQHGmpwysAwA5saMFQV3/s640/xml04.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Because of DATE data type, I change to output using the TO_CHAR function<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtG-a179Vx98zKi_p0hF6siUnZrALrKCFl8rieUFx81JK_WfUw1zk64FsEvr8tf8hS1Ec6KYZ403nV5_UWiqthe5Cr3-yoPF-BY1-uHiCRbAag1cSqG7Ae3AEfRmmS6nhLhTfR/s1600/xml05.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="174" data-original-width="844" height="130" id="id_e151_ab78_51f8_a736" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtG-a179Vx98zKi_p0hF6siUnZrALrKCFl8rieUFx81JK_WfUw1zk64FsEvr8tf8hS1Ec6KYZ403nV5_UWiqthe5Cr3-yoPF-BY1-uHiCRbAag1cSqG7Ae3AEfRmmS6nhLhTfR/s640/xml05.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div>
<br /></div>
Really simple. But I can put an XMLElement (gray and green) inside another one (red)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzAI2luWfeSMbre0vxEoUb6Eq2_DNaUujbExIaHpPm15OO_k5LtNPaJZm-vs-TdVLB-CjwkIDDSIlzuXd3591HVgOJIHdw77roGGhmEpEbF3CB4EOYRti6dzt0RM_xrsnlBMtM/s1600/xml06.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="310" data-original-width="762" height="260" id="id_41c9_816d_789f_d33e" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzAI2luWfeSMbre0vxEoUb6Eq2_DNaUujbExIaHpPm15OO_k5LtNPaJZm-vs-TdVLB-CjwkIDDSIlzuXd3591HVgOJIHdw77roGGhmEpEbF3CB4EOYRti6dzt0RM_xrsnlBMtM/s640/xml06.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<span style="font-family: "times";">
So I obtain a more complex XML.</span><br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<span style="font-family: "times";">
[1] <span style="font-size: 12pt;"><a href="https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77">https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77</a></span></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<a href="http://orasal.blogspot.it/2017/09/using-xml-10-comparing-functions.html">Part10</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-12-xmlattributes.html">Part12</a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br /></div>
</div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-42216202532878411762017-09-11T15:19:00.000+02:002017-10-11T15:40:11.296+02:00Using XML 10: Comparing functionsIn this post, I want just compare the EXTRACT and EXTRACTVALUE functions with the new XMLQuery and XMLTable functions, respectively.<br />
<br />
What I want to show is summarized in this table<br />
<br />
<div style="text-align: center;">
<table align="center" auto="" border="1" id="id_3b09_5cd7_e8f2_5272" style="font-family: courier new;">
<tbody>
<tr>
<th>Old Syntax</th>
<th>New Syntax</th>
</tr>
<tr>
<td>EXTRACT</td>
<td>XMLQuery</td>
</tr>
<tr>
<td>EXTRACTVALUE</td>
<td>XMLTabel</td>
</tr>
</tbody></table>
<div style="text-align: left;">
<br />
<br /></div>
<div style="text-align: left;">
But wait a moment. Really, EXTRACTVALUE extracted the value while in the previous example the XMLTable was the same as XMLQuery, they is EXTRACT function.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
The example for XMLTable operator not used all parameters. I not used the "COLUMN" option.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
It's the time.</div>
</div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, t.*, tx.* from mytab t, </span><br />
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 2> XMLTable('/alertlog/msg[@pid="65370"]' </span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 3> PASSING t.XML_COLUMN</span> <span style="font-family: "courier new" , "courier" , monospace;">COLUMNS message path 'msg', text path 'txt') tx</span><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" id="id_f794_1127_248e_c906" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmZz8GiPJ6JkWRtqG2BhvJzSrKujjzmf2TqKbFKDxnJXyN8GihoCqtPqYMxgBlqNWNd7JOenE3etqrPar41xKO5DPkkIekf-NGR2aGroc2retrZ5uKTGzaf4pStUpc6dQRxYBb/s1600/xml36.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="916" data-original-width="1600" height="366" id="id_33e4_9152_b22_f141" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmZz8GiPJ6JkWRtqG2BhvJzSrKujjzmf2TqKbFKDxnJXyN8GihoCqtPqYMxgBlqNWNd7JOenE3etqrPar41xKO5DPkkIekf-NGR2aGroc2retrZ5uKTGzaf4pStUpc6dQRxYBb/s640/xml36.JPG" style="height: auto; width: 640px;" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
Now you can say that XMLTable is like the EXTRACTVALUE function. So the table I wrote above is more correctly like this one:<br />
<br /></div>
<table align="center" auto="" border="1" id="id_3b09_5cd7_e8f2_5272" style="font-family: courier new;">
<tbody>
<tr>
<th>Old Syntax</th>
<th>New Syntax</th>
</tr>
<tr>
<td>EXTRACT(<xmltype>, <row-pattern>)</td>
<td>XMLQuery(<row-pattern><br />
PASSING <col-name><br />
RETURNING CONTENT)</td>
</tr>
<tr>
<td>EXTRACTVALUE(<col-name>, <row-pattern>)</td>
<td>XMLTabel(<row-pattern><br />
PASSING <expr><br />
COLUMNS <column> PATH <string>)</td>
</tr>
</tbody></table>
<br />
<div>
At this point I'm (almost) ready to modify a statement wrote by Connor McDonald. Yes. My goal when I decided to write these posts was to understand a particular statement that McDonald wrote in him <a href="https://www.youtube.com/watch?v=yKHQQXKdfOM&t=82s">video</a> [1]<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7LKQAJ_KKaQ9uSJkfTxODhoicr0-rJU2UP3LRgICNfPvx-ED9Q2lzWWhKnXkcH1BFfginmJXdM5n8JDbf0j7C61771RNPr0XKmpJHTEe_nM0avUvD54D5-qPjmnATI56-kL3a/s1600/xmlQuery01.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="404" data-original-width="716" height="360" id="id_ca8a_5c88_455f_84de" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7LKQAJ_KKaQ9uSJkfTxODhoicr0-rJU2UP3LRgICNfPvx-ED9Q2lzWWhKnXkcH1BFfginmJXdM5n8JDbf0j7C61771RNPr0XKmpJHTEe_nM0avUvD54D5-qPjmnATI56-kL3a/s640/xmlQuery01.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
The statement I used is a little different: I just modified some aliases and the way to quote the string (in <span style="color: lime;">green </span>the modifications)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">with xml </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> as (</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , dbms_xmlgen.getxmltype(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> <span style="color: lime;">q'[</span>select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , partition_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , high_value</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> dba_tab_partitions</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> where </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name='<span style="color: lime;">]'</span>||table_name||<span style="color: lime;">q'[</span>'<span style="color: lime;">]'</span>) as <span style="color: lime;">xml_output</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from dba_tables t</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> where table_name in ('COMPOSITE_RNG_LIST')</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> and owner='FRTD_CORP'</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> )</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> extractValue(rws.object_value, '/ROW/PARTITION_NAME') <span style="color: lime;">partition_name</span>,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from xml x, </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table(xmlsequence(extract(x.<span style="color: lime;">xml_output</span>, 'ROWSET/ROW'))) rws</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">order by 1,2,3;</span><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" id="id_3511_c9eb_4a58_8872" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-1eUb6HW0IEnU45tzkna0Z23wr17NsGmMu2SfHqvKnhmv0BsJJeB5NBvgVuDfajtRVHSQvnLHQgFBCqBirtNlZ4lD5ymYjuBXOnSmF6yJ4jt2XTMO_icfc0AFxfgqcHRtOfzC/s1600/xml01.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="593" data-original-width="1113" height="340" id="id_1fab_cf04_135_9383" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-1eUb6HW0IEnU45tzkna0Z23wr17NsGmMu2SfHqvKnhmv0BsJJeB5NBvgVuDfajtRVHSQvnLHQgFBCqBirtNlZ4lD5ymYjuBXOnSmF6yJ4jt2XTMO_icfc0AFxfgqcHRtOfzC/s640/xml01.JPG" style="height: auto; width: 640px;" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
What I don't discussed yet is the DBMS_XMLGEN.GETXMLTYPE. I'm going to investigate this function in the 3th part ("From rows/columns To XML" [3]) of this collection on XML.<br />
<br />
For now, from [2], we know that "<i>the PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results</i>". It should be enough in order to understand the meaning of the above statement.<br />
<br />
Before to go ahead, I need to know the format of my XML source: it comes from CTE (Common Table Expression). So I run just this statement<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhq98iuSivNnTisWIxoE8nxwvmFR1kyVdJUQcyH2I3fflgMWccHeJPMb5xiMGpZnftsmsuCtAGqVDCSHiTs-ceGmAfLnmaDWBXEthBPLfdFY1WOJxwXWzi0msRTk7ISUlM9mxEa/s1600/xml04.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="718" data-original-width="1149" height="398" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhq98iuSivNnTisWIxoE8nxwvmFR1kyVdJUQcyH2I3fflgMWccHeJPMb5xiMGpZnftsmsuCtAGqVDCSHiTs-ceGmAfLnmaDWBXEthBPLfdFY1WOJxwXWzi0msRTk7ISUlM9mxEa/s640/xml04.JPG" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
Comparing this output with the one I used in my examples, you can build following map<br />
<br />
<div style="align: center;">
<table align="center" auto="" border="1" id="id_3b09_5cd7_e8f2_5272" style="font-family: courier new;">
<tbody>
<tr>
<th>McDonald case</th>
<th>My example</th>
</tr>
<tr>
<td>ROWSET</td>
<td>alertlog</td>
</tr>
<tr>
<td>ROW</td>
<td>msg</td>
</tr>
<tr>
<td>TABLE_NAME</td>
<td>txt</td>
</tr>
<tr>
<td>PARTITION_NAME</td>
<td>txt</td>
</tr>
<tr>
<td>HIGH_VALUE</td>
<td>txt</td>
</tr>
</tbody></table>
<br /></div>
Based on all you saw untill now, I can rewrite the above statement like this one (in a red square the two core pieces)<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">with xml </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> as (</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> select</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , dbms_xmlgen.getxmltype(</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> q'[select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , partition_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , high_value</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> dba_tab_partitions</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> where </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name= ']'||table_name||q'[']') as xml_output</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> from dba_tables t</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> where table_name in ('COMPOSITE_RNG_LIST')</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> and owner='FRTD_CORP'</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> )</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">select </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> rws.table_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , rws.partition_name</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , rws.high_value</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">from </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> xml x</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , xmltable('/ROWSET/ROW'</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> PASSING</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> x.xml_output </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> COLUMNS </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> table_name PATH 'TABLE_NAME'</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , partition_name PATH 'PARTITION_NAME'</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> , high_value PATH 'HIGH_VALUE') rws</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">order by 1,2,3;</span><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" id="id_bc91_75a8_7342_d676" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9caYCKwoAFd_S_n3RtFDYlYcWb4VuawvY6iOUbKNYHNkD8TKE6a68n_sA7zjDyV9jwbuTYCzZtBeq-DeIOSi-nXW6o2zy_MBXF1Wffq3A2MEgEFVr1m2Yy0AVpPbKoeUml_1n/s1600/xml02.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="707" data-original-width="1110" height="406" id="id_7123_db33_8bcd_aea5" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9caYCKwoAFd_S_n3RtFDYlYcWb4VuawvY6iOUbKNYHNkD8TKE6a68n_sA7zjDyV9jwbuTYCzZtBeq-DeIOSi-nXW6o2zy_MBXF1Wffq3A2MEgEFVr1m2Yy0AVpPbKoeUml_1n/s640/xml02.JPG" style="height: auto; width: 640px;" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
Pay attention on the case of you are searching. If you remember, XML is case sensitive, so you must specify ROWSET, ROW and others in upper case. Infact if you run the previous statement using the search in lower case you found noting:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMRrB9zWFi4Q9mV5NtqLTTx3tRzX4pj_JHoAfPlUd6y2Mv8o7Te8GLY6qjAePh8KS9Blpnpl32J4E_FwoIQM3HwJtjf7pdSoOQdvRYyWdkJSJvo3K5rDeBLozJGbt6klzlulph/s1600/xml05.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="570" data-original-width="545" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMRrB9zWFi4Q9mV5NtqLTTx3tRzX4pj_JHoAfPlUd6y2Mv8o7Te8GLY6qjAePh8KS9Blpnpl32J4E_FwoIQM3HwJtjf7pdSoOQdvRYyWdkJSJvo3K5rDeBLozJGbt6klzlulph/s640/xml05.JPG" width="610" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">XML is case sensitive</td></tr>
</tbody></table>
<br />
For semplicity, the following picture I just put together both "select" pieces about old and new syntax.<br />
<div>
<br /></div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" id="id_97dd_7900_1367_de6b" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigK-62hldo6aQb7_Jm-Bs7tSRTP9a7O8Zsa_4by_cvF8nwdUm-DBvBJMWvRK25HiutKod_zRkbBpB70aiuffII7n2LkjwQyqxzaJhd6fzMrOYt5nyOBvUQvLoZ4lHat0p19v1G/s1600/xml03.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="228" data-original-width="1192" height="122" id="id_fe19_5e06_8e86_464b" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigK-62hldo6aQb7_Jm-Bs7tSRTP9a7O8Zsa_4by_cvF8nwdUm-DBvBJMWvRK25HiutKod_zRkbBpB70aiuffII7n2LkjwQyqxzaJhd6fzMrOYt5nyOBvUQvLoZ4lHat0p19v1G/s640/xml03.JPG" style="height: auto; width: 640px;" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<h2>
Update</h2>
</div>
<div>
2017/Sep/10 - Added the section about Connor McDonald.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
2017/Sep/11 - Added the case sensitive example<br />
<h2>
Reference</h2>
[1] https://www.youtube.com/watch?v=yKHQQXKdfOM&t=82s<br />
[2] https://docs.oracle.com/database/122/ARPLS/DBMS_XMLGEN.htm#ARPLS374</div>
<div>
[3] http://orasal.blogspot.com/2017/09/using-xml-argument-index.html<br />
<br /></div>
<a href="http://orasal.blogspot.com/2017/09/using-xml-09-select-part-33b.html" id="id_7eb8_5732_9d4b_2f2d" target="_self">Part09</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index </a><a href="http://orasal.blogspot.it/2017/09/using-xml-12-xmlelement.html">Part11</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-21143290726021236102017-09-11T15:18:00.000+02:002017-10-11T15:53:46.217+02:00Using XML 09: SELECT, Part 3b/3In the <a href="http://orasal.blogspot.it/2017/09/using-xml-08-select-part-33a.html" id="id_762a_43da_88ca_8051">previous post</a> I saw that the XQuery_string '/alertlog' return just one row. So my question is "what if I change the row-pattern from '/alertlog' to /alertlog/msg or /alertlog/msg/txt"?<br />
<br />
Here the answere<br />
<br />
<h3>
XQuery_string: /alertlog/msg</h3>
<div>
<br />
As you can see, in this case there will be 6 rows: one for each element. And this is exactly what I want<br />
<br /></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, t.*, tx.* from mytab t, </span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 2> XMLTable('<span style="color: #ff1744;">/alertlog/msg</span>' PASSING t.XML_COLUMN) tx</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXZP1Q92pMLYpeYzk8Eg8ZZkI8gjts9LnqcJjH9U7tjeYg2JFb72op9jjVr4wIaAhTv7-_oPGLMI5pHOTBS8USnAl3YpFHzYqpR70_LnJQsZ6RhcNg1AhC5LcDdVaeJBtiLyUI/s1600/xml31a.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="907" data-original-width="1175" height="494" id="id_b272_fcee_12b4_ea21" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXZP1Q92pMLYpeYzk8Eg8ZZkI8gjts9LnqcJjH9U7tjeYg2JFb72op9jjVr4wIaAhTv7-_oPGLMI5pHOTBS8USnAl3YpFHzYqpR70_LnJQsZ6RhcNg1AhC5LcDdVaeJBtiLyUI/s640/xml31a.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ7cbwegSaFofPbxeCI0RFCDemG4BmgLr7Wba3fJZhNOHmUcc6rtaoVxA0w1R4Uld_YzsPTIk8MyuM5pAMjbE0IlSajegb6VAaH5Fy-EdkOk9zeg2UGHaXCjHgGcoBNu6vVTRJ/s1600/xml31b.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="818" data-original-width="1178" height="444" id="id_e8f5_a1e9_eaa8_6dae" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ7cbwegSaFofPbxeCI0RFCDemG4BmgLr7Wba3fJZhNOHmUcc6rtaoVxA0w1R4Uld_YzsPTIk8MyuM5pAMjbE0IlSajegb6VAaH5Fy-EdkOk9zeg2UGHaXCjHgGcoBNu6vVTRJ/s640/xml31b.JPG" style="height: auto; width: 637px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh16giwch-l2ap_BeLjzY7LSKw0hMHU0qBMtDidAH6FzOu3eUveWpgreIE0wRX-xBRkebPu3vKGsgikqxqm9XJjkI0CSeHOb4DZIkBWgSWCnEjQLgtkb0CDK2yTUHDmw24GUjbI/s1600/xml31c.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="812" data-original-width="1172" height="442" id="id_9b58_7c51_dff8_c430" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh16giwch-l2ap_BeLjzY7LSKw0hMHU0qBMtDidAH6FzOu3eUveWpgreIE0wRX-xBRkebPu3vKGsgikqxqm9XJjkI0CSeHOb4DZIkBWgSWCnEjQLgtkb0CDK2yTUHDmw24GUjbI/s640/xml31c.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7fYGuJYTyZ7VeRLPREJ-ec0G6s0S41bc8n8EabxRkpAhDU7AE_Es7dIRRqpgdpgh3DZUOiL5x5VFFCLfx9XB8pn0tHx9MInecV5QSM8ZgPss5fLqnsmIVHz3Mz3NBASLI97qy/s1600/xml31d.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="814" data-original-width="1185" height="438" id="id_d594_704_dc27_d633" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7fYGuJYTyZ7VeRLPREJ-ec0G6s0S41bc8n8EabxRkpAhDU7AE_Es7dIRRqpgdpgh3DZUOiL5x5VFFCLfx9XB8pn0tHx9MInecV5QSM8ZgPss5fLqnsmIVHz3Mz3NBASLI97qy/s640/xml31d.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIudBi7dddDtdw8IzMQLfO1IBvxjfrLP8gx4W4_EU3lGUgI0fzH3SKu_hu1McPKe9TPtv9HUO6M5i3xSpSWK8_kMeUDLSAxHDDsrWoD2oTVNlg5meiHZEyIq9XKiIIzEDEP3ar/s1600/xml31e.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="822" data-original-width="1197" height="438" id="id_dae4_435f_ca9b_1183" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIudBi7dddDtdw8IzMQLfO1IBvxjfrLP8gx4W4_EU3lGUgI0fzH3SKu_hu1McPKe9TPtv9HUO6M5i3xSpSWK8_kMeUDLSAxHDDsrWoD2oTVNlg5meiHZEyIq9XKiIIzEDEP3ar/s640/xml31e.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCVDXvIYvdi2MLC0SBBh3V2Ab3NsssJtEhvIP1DGb_xkK6FHJhL9j1z29dJMIKpraFz-0XDjh1JS-dMXPtmu6jvA4EQhywcNPkBdx2dd4XwLGFHFK3gcxiLeJagtpLJ5j_cM-V/s1600/xml31f.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="862" data-original-width="1173" height="470" id="id_9cc8_a8c8_ff0a_7bfa" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCVDXvIYvdi2MLC0SBBh3V2Ab3NsssJtEhvIP1DGb_xkK6FHJhL9j1z29dJMIKpraFz-0XDjh1JS-dMXPtmu6jvA4EQhywcNPkBdx2dd4XwLGFHFK3gcxiLeJagtpLJ5j_cM-V/s640/xml31f.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<br />
<h3>
XQuery_string: /alertlog/msg/txt</h3>
<br />
Also in this case I have 6 rows. But here I have only the <txt> element (it was my request)<br />
<br />
<div>
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, t.*, tx.* from mytab t, </span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 2> XMLTable('<span style="color: #ff1744;">/alertlog/msg/txt</span>' PASSING t.XML_COLUMN) tx</span></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsFyd8KdlG2KiNPXpZl1qhH2Smy5d0KF2QSpqAtux4u5EbJDo0nzNIzKJj-z8C2UhjAXPjTyqQdVrJJGAvsGtE4coxih2hp2exDm960ahlXZ2U7BKvRYalnpZVCX2qUelKR_nb/s1600/xml32a.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="868" data-original-width="1178" height="470" id="id_72e6_b5f6_492d_785d" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsFyd8KdlG2KiNPXpZl1qhH2Smy5d0KF2QSpqAtux4u5EbJDo0nzNIzKJj-z8C2UhjAXPjTyqQdVrJJGAvsGtE4coxih2hp2exDm960ahlXZ2U7BKvRYalnpZVCX2qUelKR_nb/s640/xml32a.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div>
<br />
I skip the output between line 2 and 5.</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDrUBr4Y5TPhWPc0aVA26DCH8Z7Vkq44MlzAn-jNask9Rb7-2JSC3keHiUibc9FSuEi8RkKP6XAwn1Szror7qizGLs3X6cYH092CEfDE1Wk1I_aWB6xbAlLiADhZGcAKajkd8e/s1600/xml32b.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="867" data-original-width="1183" height="468" id="id_873b_e9a_4e70_df99" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDrUBr4Y5TPhWPc0aVA26DCH8Z7Vkq44MlzAn-jNask9Rb7-2JSC3keHiUibc9FSuEi8RkKP6XAwn1Szror7qizGLs3X6cYH092CEfDE1Wk1I_aWB6xbAlLiADhZGcAKajkd8e/s640/xml32b.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<br />
<h3>
XQuery_string: more examples</h3>
<br />
Here other two example: similar to the previous posts<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, t.*, tx.* from mytab t, </span><br />
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 2> XMLTable('<span style="color: #ff1744;">/alertlog/msg/[@pid="65370"]</span>' PASSING t.XML_COLUMN) tx</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7lcOvyKuoYkSOH56DZN9TxyEDL4f3sHjw4fRhWvyCr1yOsQF6Mb5AtZJbgObG4gUj_F2BbUT9mN6PXism7Magr5wSlt1otEzL1LS2latKvJgoNAXW_hA3uFPGAtglbMUndHmy/s1600/xml33.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="888" data-original-width="1176" height="482" id="id_6c59_c7f2_1df2_3002" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7lcOvyKuoYkSOH56DZN9TxyEDL4f3sHjw4fRhWvyCr1yOsQF6Mb5AtZJbgObG4gUj_F2BbUT9mN6PXism7Magr5wSlt1otEzL1LS2latKvJgoNAXW_hA3uFPGAtglbMUndHmy/s640/xml33.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, t.*, tx.* from mytab t, </span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 2> XMLTable('<span style="color: #ff1744;">/alertlog/msg/</span></span><span style="font-family: "courier new" , "courier" , monospace;"><span style="color: #ff1744;">[@pid="65263"]</span></span><span style="font-family: "courier new" , "courier" , monospace;">' PASSING t.XML_COLUMN) tx</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGNPnBLGwgwdqbUCwZSwjHJqSjevknALGME6WxG6WbwynyO2WoF2DEsFzbrgonI9IGEzvU3_rR0OmJkAhP_mHAFKT_cxKTGU2tXOVHwePJ8S9WvbzF_zKMOzzP5fYEMrHq_q_6/s1600/xml34a.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="872" data-original-width="1179" height="472" id="id_2545_d39d_bbf1_166a" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGNPnBLGwgwdqbUCwZSwjHJqSjevknALGME6WxG6WbwynyO2WoF2DEsFzbrgonI9IGEzvU3_rR0OmJkAhP_mHAFKT_cxKTGU2tXOVHwePJ8S9WvbzF_zKMOzzP5fYEMrHq_q_6/s640/xml34a.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMlYUdLUuls49IxA0Q33Q2q82ODRHREwkT4oI9dT9D8p1WUVle7WMYgf-7Iv2kcTJxYQAVcop-NjuDSlmsNpmmJKUcU2QJkbwqkkNFs1yFbaCiyqwGLcu_6nu0zMzqUfyXyaeB/s1600/xml34b.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="809" data-original-width="1155" height="448" id="id_8e9e_757a_d923_95c9" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMlYUdLUuls49IxA0Q33Q2q82ODRHREwkT4oI9dT9D8p1WUVle7WMYgf-7Iv2kcTJxYQAVcop-NjuDSlmsNpmmJKUcU2QJkbwqkkNFs1yFbaCiyqwGLcu_6nu0zMzqUfyXyaeB/s640/xml34b.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjSYyHi5KKRHrVNTbIqzggMyJUIPuojwxN60fdPD3FKOyTtlZPzrXL3QZ-D44zOA00Il1ur5Yw9ydDhbDB6scCysBBwfhm5KsAvIX5QocZgUd81XM5AGV2Qt9TUgHkuyhXKrDb/s1600/xml34c.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="807" data-original-width="1133" height="454" id="id_df3_3555_1601_c697" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjSYyHi5KKRHrVNTbIqzggMyJUIPuojwxN60fdPD3FKOyTtlZPzrXL3QZ-D44zOA00Il1ur5Yw9ydDhbDB6scCysBBwfhm5KsAvIX5QocZgUd81XM5AGV2Qt9TUgHkuyhXKrDb/s640/xml34c.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
and if I modify the search<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, t.*, tx.* from mytab t, </span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> 2> XMLTable('<span style="color: #ff1744;">/alertlog/msg/[@pid="65370"]/txt</span>' PASSING t.XML_COLUMN) tx</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdy0guZvuXkPrWJ1gri61XnfBhdKKVOZjpuJ_zG9SlZWWEQR7P79RIGfY3Z016S-CXfQfmE0EjGgvhrIIB8OvVBnW22zOfpA7c7oiI32onN0xocV0Uh-HnstxLFyG446-bIZmK/s1600/xml35c.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="879" data-original-width="1186" height="474" id="id_225_90f8_5fb5_563d" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdy0guZvuXkPrWJ1gri61XnfBhdKKVOZjpuJ_zG9SlZWWEQR7P79RIGfY3Z016S-CXfQfmE0EjGgvhrIIB8OvVBnW22zOfpA7c7oiI32onN0xocV0Uh-HnstxLFyG446-bIZmK/s640/xml35c.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<a href="http://orasal.blogspot.it/2017/09/using-xml-08-select-part-33a.html" id="id_5f09_dfc3_6d3a_f146" target="_self">Part08</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-10-comparing-functions.html" id="id_3ec9_77e_6c6c_138e" target="_self">Part10</a></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-58386033878694396962017-09-11T14:55:00.000+02:002017-10-11T15:33:13.489+02:00Using XML 08: SELECT, Part 3a/3Continune from <a href="http://orasal.blogspot.it/2017/08/using-xml-07-select-part-23.html">previous post</a><br />
<br />
<h3>
XMLTable [4]</h3>
XMLTable decompose the result of an XQuery evaluation into relational rows and columns. Or: the XMLTABLE operator allows us to split the XML data into rows and project columns on to it [13]. You can query the result returned by the function as a virtual relational table using SQL.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">XMLTABLE (<XQuery_string</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XMLTABLE_options</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> |- </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XMLTABLE_options</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> =</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XML_passing_clause</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> [COLUMNS </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XML_table_colums</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">]</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> |- </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XML_passing_clause</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> =</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> PASSING </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">expr</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> [AS </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">identifier</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">]</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> |- </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XML_table_colums</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> =</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">column</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> [</span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">datatype>|XMLTYPE] [PATH </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">string</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">] [DEFAULT </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">expr</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">]</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
where [4][5]<br />
<ul>
<li><<i>XQuery_string</i>> is a literal string. <i>XQuery_string</i> is sometimes called the row pattern of the XMLTable call. It is a complete XQuery expression. The value of XQuery_string serves as input to the XMLTable function; it is this XQuery result that is decomposed and stored as relational data.</li>
<li><u>The <expr> in the XML_passing_clause is an expression returning an XMLType or an instance of a SQL scalar data type</u> (that is, not an object or collection data type) that is used as the context for evaluating the XQuery expression. You can specify only one <i>expr </i>in the PASSING clause without an identifier. <u>Each expression <i>expr</i> can be a table or view column value, a PL/SQL variable, or a bind variables with proper casting</u>. The result of evaluating each expr is bound to the corresponding identifier in the <i>XQuery_string</i>. If any expr that is not followed by an AS clause, then the result of evaluating that expression is used as the context item for evaluating the <i>XQuery_string</i></li>
<li>The optional <i>COLUMNS </i>clause defines the columns of the virtual table to be created by XMLTable. <u>If you omit the <i>COLUMNS </i>clause, then XMLTable returns a row with a single XMLType pseudocolumn named COLUMN_VALUE</u>. For each resulting column you must specify the column data type, which can be XMLType or any other data type. If the column data type is XMLType, then specify the XMLTYPE clause.</li>
<li>If you omit PATH, then the XQuery expression column is assumed. For example:</li>
</ul>
XMLTable(... COLUMNS xyz)<br />
<ul></ul>
is equivalent to<br />
<ul></ul>
XMLTable(... COLUMNS xyz PATH 'XYZ')<br />
<ul></ul>
You can use different PATH clauses to split the XQuery result into different virtual-table columns.<br />
<div>
<br />
<br />
In a more simple way<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">XMLTABLE (</span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">row-pattern</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> PASSING </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XMLType</span><span style="font-family: "courier new" , "courier" , monospace;">></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> COLUMNS <column_name> </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">datatype</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> PATH </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">string</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">) </span><br />
<br />
<br />
Ok. Let's start<br />
<br />
The row-pattern is '/alertlog'; the PASSING clause is......well, I try "SELECT * FROM MYTAB" (because <span style="-webkit-text-size-adjust: auto; background-color: rgba(255, 255, 255, 0);"><i>Each expression expr can be a table or view column value</i></span><u style="-webkit-text-size-adjust: auto; background-color: rgba(255, 255, 255, 0);">)</u><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select * from mytab))</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhgM7X8Jn2gwvDKHtUwd4YvdSthlplMtLYKid1LFExJCRtQJJku3t2EAFCaLVhKSRGuqOjUKoJqmKGDCGZ1rCXSRg14LfJpHGeT1MMrEPwvXnTleU0fgvgkUALhcMtfzePPFun/s1600/xml25.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="121" data-original-width="798" height="96" id="id_bf0a_2f39_c952_7873" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhgM7X8Jn2gwvDKHtUwd4YvdSthlplMtLYKid1LFExJCRtQJJku3t2EAFCaLVhKSRGuqOjUKoJqmKGDCGZ1rCXSRg14LfJpHGeT1MMrEPwvXnTleU0fgvgkUALhcMtfzePPFun/s640/xml25.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL> SELECT * FROM XMLTable('/alertlog' PASSING (select KEY_COLUMN, XML_COLUMN from mytab))</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjT7wiuh5oTyaojC0fFqoLzoSZ9bgreNAJ_tGr1rgpZTgumQZKnJvpkKLZJywNdaVO1jh3K9DpVAhnr11HR5Yj7KcQIfoPCubXHQrwi8v22r-4aXF2SGzgoySksPjUQwFrJrY0P/s1600/xml25b.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="121" data-original-width="959" height="80" id="id_b8e0_360f_ae1a_f016" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjT7wiuh5oTyaojC0fFqoLzoSZ9bgreNAJ_tGr1rgpZTgumQZKnJvpkKLZJywNdaVO1jh3K9DpVAhnr11HR5Yj7KcQIfoPCubXHQrwi8v22r-4aXF2SGzgoySksPjUQwFrJrY0P/s640/xml25b.JPG" style="height: auto; width: 640px;" width="640" /></a>De</div>
<div>
<new_column_name><column_type><xquery_path><br /></xquery_path></column_type></new_column_name></div>
Ok. <exp> can be a table or view column name, but it must <i>return an XML or an instance of a SQL scalar data type</i>. So I have to try something simple. Because MYTAB table is<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJcF2qiIEdwdke5PnG9lhHq03Bg8oMqEqUqdThez-203PW9qWFlq-zDiYD7gGtXwV2YsrGMI-2-4rvhHPFJCbZ4WFlyAjxwKBZ7GiJyWI9PsJOEAvo_adw2twUapns8bI0Eqb4/s1600/xml02.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="192" data-original-width="747" height="164" id="id_cfca_8f6e_1f12_1b5" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJcF2qiIEdwdke5PnG9lhHq03Bg8oMqEqUqdThez-203PW9qWFlq-zDiYD7gGtXwV2YsrGMI-2-4rvhHPFJCbZ4WFlyAjxwKBZ7GiJyWI9PsJOEAvo_adw2twUapns8bI0Eqb4/s640/xml02.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
I specify just the MYTAB.XML_COLUMN column<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL></span><span style="font-family: "courier new" , "courier" , monospace; text-align: center;"><span style="font-size: 12.8px;"> </span><span style="font-size: x-small;">SELECT * </span></span><span style="font-family: "courier new" , "courier" , monospace; text-align: center;"><span style="font-size: x-small;">FROM XMLTable</span><span style="font-size: 12.8px;">('/alertlog' PASSING (<span style="color: #ff1744;">select XML_COLUMN from mytab</span>))</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKV8G7IEg2rRLWgDkS7suxELFPALx8b4lnb0iJ14KFotQQ7gqi3QF5IfRRNWzzxtk2PiSN5kimvHkdIT8-dWeBANbwUEdOgLMfyjbHO6OqLF_HHHhWCnUF7SKHshUCUlOHIgTG/s1600/xml26.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="616" data-original-width="1220" height="322" id="id_8a86_d8cc_9013_b791" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKV8G7IEg2rRLWgDkS7suxELFPALx8b4lnb0iJ14KFotQQ7gqi3QF5IfRRNWzzxtk2PiSN5kimvHkdIT8-dWeBANbwUEdOgLMfyjbHO6OqLF_HHHhWCnUF7SKHshUCUlOHIgTG/s640/xml26.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Remember that <i>if you omit the COLUMNS clause, then XMLTable returns a row with a single XMLType pseudocolumn named COLUMN_VALUE</i>.<br />
<br />
I want to know how many rows are returned, so I use thr ROWNUM pseudo column<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL> col COLUMN_VALUE for a60</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL> <span style="color: #ff1744;">SELECT rownum, t.*</span></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"> </span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">FROM XMLTable('/alertlog' PASSING (<span style="color: #ff1744;">select XML_COLUMN from mytab</span>)) t</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg911kjw_W51qqU2Spk_mJT6ZLMY-RV_gmTUgNK0QO5a8DMAaXY8M2Nz5u3MLoUjgGDenuwNcP4V3wE5CRNggEH0Uk5j1okAAUohMugukNYdfWB_8Ggfvc5d_krRq7n6k6johX0/s1600/xml27.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="915" data-original-width="972" height="600" id="id_503d_1084_624b_841c" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg911kjw_W51qqU2Spk_mJT6ZLMY-RV_gmTUgNK0QO5a8DMAaXY8M2Nz5u3MLoUjgGDenuwNcP4V3wE5CRNggEH0Uk5j1okAAUohMugukNYdfWB_8Ggfvc5d_krRq7n6k6johX0/s640/xml27.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Perfect. Now because I want all columns of MYTAB, the only way I have in my mind is to join the XMLTable with MYTAB, but I'not not sure: I try<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL> SELECT tk.key_column, tx.*</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">SQL</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"> FROM <span style="color: #ff1744;">XMLTable('/alertlog' PASSING (select XML_COULMN from mytab)) tx</span>,<span style="color: #ff1744;"> mytab t</span>;</span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinxvIH9hOKnXGRIDnfDSro1PUmsXb_RmtcmNKR4h3ioR4DvMvdzIYldB6u0dQK82_FDGZKA-zMc_FfvbMGn7yCbhnUEl1vTtvrUMPbbeaqcErq0bzfnPK_JE7al_u00m1a73lp/s1600/xml28.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="868" data-original-width="1121" height="494" id="id_a63e_d6b6_55fb_a9a2" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinxvIH9hOKnXGRIDnfDSro1PUmsXb_RmtcmNKR4h3ioR4DvMvdzIYldB6u0dQK82_FDGZKA-zMc_FfvbMGn7yCbhnUEl1vTtvrUMPbbeaqcErq0bzfnPK_JE7al_u00m1a73lp/s640/xml28.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
I tried to use the the new syntax in Oracle: the "JOIN" clause. See the Appendix section at the end of this post.<br />
<br />
In the previous SELECT, I specified "tk.key_column" and "tx.xml_column". I would like not specify the columns, so I change previous statement just aa little<br />
<br />
<span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;">SQL</span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">></span><span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;"> SELECT * FROM <span style="color: #ff1744;">XMLTable('/alertlog' PASSING t.XML_COLUMN) tx</span>,<span style="color: #ff1744;"> </span><span style="color: #283593;">mytab t</span></span></span><br />
<span style="font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTFpAzCq6EYcSdbqJN9qyI75dtpZpnz9mIMiBk2pwVCEt4FLV5sIiYYaRD_n2bAeqF_PYts8cVEfBe3yNY2vNACkmT70zxKwmp79o2h_P0Z7wVx_hKpt76XZxmGpbGavweQWPI/s1600/xml29.JPG" imageanchor="1"><img border="0" data-original-height="128" data-original-width="835" height="98" id="id_a854_cca7_dfc1_4792" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTFpAzCq6EYcSdbqJN9qyI75dtpZpnz9mIMiBk2pwVCEt4FLV5sIiYYaRD_n2bAeqF_PYts8cVEfBe3yNY2vNACkmT70zxKwmp79o2h_P0Z7wVx_hKpt76XZxmGpbGavweQWPI/s640/xml29.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
I want to try a stupid thing: I want to change the order between XMLTable and MYTAB. Why? Because in the exception ORA-00904, Oracle told me that the "T.XML_COLUMN" is an invalid identifier. It's strange, because I specified the "T" table just after the comma in the FROM clause.<br />
<br />
<span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;">SQL</span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">></span><span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;"> SELECT * FROM </span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><span style="color: #283593;">mytab t</span>,<span style="color: #ff1744;"> </span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><span style="color: #ff1744;">XMLTable('/alertlog' PASSING t.XML_COLUMN) tx</span> </span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKVLNwI5rQx2W6TQApTpdafGRCJicnwTOz6btW9Z5RSAnljSJ6y6LXS3FxVM1q6X_LMdkkWN8fTDWP1ZYUHQUI8EYWnk3gwaX-ZnIaI42xPL8dk7L3SljpGFGHCqZAyJxChQOV/s1600/xml30.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="887" data-original-width="1096" height="516" id="id_2d1f_efd4_2a71_d6a0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKVLNwI5rQx2W6TQApTpdafGRCJicnwTOz6btW9Z5RSAnljSJ6y6LXS3FxVM1q6X_LMdkkWN8fTDWP1ZYUHQUI8EYWnk3gwaX-ZnIaI42xPL8dk7L3SljpGFGHCqZAyJxChQOV/s640/xml30.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Great, but I want to show also the row numebr<br />
<br />
<span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;">SQL</span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">></span><span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;"> SELECT rownum, t.*, tx.*</span></span><br />
<span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;">SQL</span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">></span><span style="font-size: 12.8px; text-align: center;"><span style="font-family: "courier new" , "courier" , monospace;"> FROM </span></span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">mytab t, </span><span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;">XMLTable('/alertlog' PASSING t.XML_COLUMN) tx </span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: 12.8px; text-align: center;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZ6TGXarMS7UsfrVy52tzPmDrx_kKgTKlcakKSBhfUwyp7QJDBYQcBIbbeaOB4n7hmv95J6Gzf7X5S5fw7tjXNH67jfZMf7Exhv6HNCQ09yvE10iRs-Av_3owwWguThs7ip2Qt/s1600/xml30b.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="905" data-original-width="1176" height="492" id="id_3c6f_6592_fde1_a8a2" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZ6TGXarMS7UsfrVy52tzPmDrx_kKgTKlcakKSBhfUwyp7QJDBYQcBIbbeaOB4n7hmv95J6Gzf7X5S5fw7tjXNH67jfZMf7Exhv6HNCQ09yvE10iRs-Av_3owwWguThs7ip2Qt/s640/xml30b.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
As usual, one row is returned.<br />
<br />
What if I change the <i>XQuery_string</i>/<i>row-pattern</i>, from <i>/alertlog</i> to <i>/alertlog/msg</i>?<br />
<br />
(to be continued)<br />
<br />
<br />
<br />
<br />
Appendix<br />
<br />
If I use the JOIN syntax I had following error</div>
<div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjk7eoBvWHQm_HelpcYhCKq12u8LozySW74LhKq9mQ1uhJyri_FAU00eJmo0YMPh-9YBXsdrdSUJGMXSauLCNBAJLEZVaxaS2UCtBmP2fxPZrvhn3Tf6-CeRz37qwNOjLA4gRcC/s1600/xml28b.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="145" data-original-width="615" height="150" id="id_8006_d294_32e3_f242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjk7eoBvWHQm_HelpcYhCKq12u8LozySW74LhKq9mQ1uhJyri_FAU00eJmo0YMPh-9YBXsdrdSUJGMXSauLCNBAJLEZVaxaS2UCtBmP2fxPZrvhn3Tf6-CeRz37qwNOjLA4gRcC/s640/xml28b.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div>
<br /></div>
It's new for me. The problem is the absence of ON clause. In fact if I use it with the condition "1=1", then it works</div>
<div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbnwgecnFIlUTf_dXUbCk6j_Rmi9kEvzJQdcTw1SihvUNx1I0uYe0EPcJ1LNXZw_4hkd3D00DxDFjsEqGiQ75uEBSM-l56lI_1QJoAhMTkE5IyQhv2OE8FGznA6cgMjrsyN-2f/s1600/xml28c.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="917" data-original-width="618" height="640" id="id_2630_fcaa_2445_7f5d" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbnwgecnFIlUTf_dXUbCk6j_Rmi9kEvzJQdcTw1SihvUNx1I0uYe0EPcJ1LNXZw_4hkd3D00DxDFjsEqGiQ75uEBSM-l56lI_1QJoAhMTkE5IyQhv2OE8FGznA6cgMjrsyN-2f/s640/xml28c.JPG" style="height: auto; width: 430px;" width="430" /></a></div>
<br />
<br />
It non depend on the join that include the XMLTable: the ON condition is mandatory for the syntax. Well, as I told before, it's the first time I incurred in the errore. This is because I always used the ON.<br />
<br />
<h3>
Update</h3>
2017/Sep/11 - Update the Reference section adding [13] and [14]<br />
<br />
<h3>
References</h3>
[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640<br />
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173<br />
[3] https://docs.oracle.com/database/122/SQLRF/XMLQUERY.htm#SQLRF06209<br />
[4] https://docs.oracle.com/database/122/SQLRF/XMLTABLE.htm#SQLRF06232<br />
[5] https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB5121<br />
[6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/<br />
[7] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/<br />
[8] https://www.youtube.com/watch?v=n_dEb6myFH4<br />
[9] https://www.youtube.com/watch?v=5d4fZVxFx20<br />
[10] <a href="https://books.google.it/books?id=gUPWAQAAQBAJ&pg=PA481&lpg=PA481&dq=oracle+xmltable+vs+extractvalue&source=bl&ots=WZr_HVzMi7&sig=3G0Q7Mqvlr6RitkLBzg8-7Ap7-A&hl=it&sa=X&ved=0ahUKEwj3rMCQ_P7VAhVDvBQKHTYyDtE4FBDoAQhUMAY#v=onepage&q=oracle%20xmltable%20vs%20extractvalue&f=false">Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress</a>]<br />
[11] http://www.ateam-oracle.com/using-xmltable-and-xmltype-to-extract-html-clob-data/<br />
[12] http://www.oradev.com/xml_functions.html<br />
[13] <a href="https://docs.oracle.com/database/121/ADXDB/xdb03usg.htm#ADXDB4052" style="font-family: Menlo, serif;">https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql</a><br />
[14] <a href="https://www.wiscorp.com/H2-2004-039-xmltable.pdf">XMLTable</a> (<a href="https://drive.google.com/open?id=0B8fWOVWyjKLMUUxLeWVoMnpRLXM">PDF</a>)<br />
<br />
<div class="MsoNormal">
<span class="MsoHyperlink"><span lang="EN-US" style="background: white; color: #222222; font-family: "helvetica neue"; font-size: 10.5pt;"><o:p></o:p></span></span></div>
</div>
<div>
<br /></div>
<a href="http://orasal.blogspot.com/2017/08/using-xml-07-select-part-23.html" id="id_8628_6217_406c_75d3">Part07</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index </a><a href="http://orasal.blogspot.com/2017/09/using-xml-09-select-part-33b.html" id="id_4952_8f16_ccf4_59aa">Part09</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-69792206894717505092017-08-31T12:18:00.001+02:002017-10-11T15:32:53.678+02:00Using XML 07: SELECT, Part 2/3Because the EXTRACT [1] and EXTRACTVALUE [2] functions are deprecated in 11gR2 [10], you have to use the XMLQuery [3] and XMLTable [4] (respectively).<br />
<br />
<h3>
XMLQuery [1]</h3>
XMLQUERY lets you query XML data in SQL statements. It takes an XQuery expression as a string literal, an optional context item, and other bind variables and returns the result of evaluating the XQuery expression.<br />
<div>
<br /></div>
<div>
The more simple syntax is</div>
<div>
<br /></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">XMLQuery(XQuery_string PASSING expr RETURNING CONTENT)</span></div>
<div>
<br /></div>
where [1]<br />
<br />
<ul>
<li>XQuery_string is a complete XQuery expression, including prolog</li>
<li>The expr is an expression returning an XMLType or an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression. </li>
<li>RETURNING CONTENT indicates that the result from the XQuery evaluation is either an XML 1.0 document or a document fragment conforming to the XML 1.0 semantics.</li>
</ul>
or in simple words<br />
<ul>
<li>XMLQueryis the search path</li>
<li>"expr" is the XML column of the table </li>
</ul>
<div>
Here there are 3 examples, so you can compare the output, with the one of EXTRACT function provided in the <a href="http://orasal.blogspot.it/2017/08/using-xml-06-select-part-12.html">previous post</a></div>
<div>
<br /></div>
<span style="font-family: "courier new" , "courier" , monospace;">SQL> SELECT rownum, </span><span style="font-family: "courier new" , "courier" , monospace;">t.*,</span><span style="font-family: "courier new" , "courier" , monospace;"> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> 2</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">XMLQuery('/alertlog' PASSING t.XML_COLUMN RETURNING CONTENT) extract FROM mytab t</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9En5aMIMDyF2alKf_LifYyL8yBpVzEW0_996rKk_CXj2YcxtV8cdpdNKs1ts1W7jK_R7LGNYAyQpvK_Holwgv1oixAKbLeOYkvS-fcLZKeyXO9TfeDQ8Whf5Og2didDBVFMeM/s1600/xml18.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="892" data-original-width="1177" height="484" id="id_941d_338e_6474_3b7" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9En5aMIMDyF2alKf_LifYyL8yBpVzEW0_996rKk_CXj2YcxtV8cdpdNKs1ts1W7jK_R7LGNYAyQpvK_Holwgv1oixAKbLeOYkvS-fcLZKeyXO9TfeDQ8Whf5Og2didDBVFMeM/s640/xml18.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<span style="font-family: "courier new" , "courier" , monospace;">SQL></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">SELECT rownum, t.*, </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> 2</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">XMLQuery('/alertlog/msg' PASSING t.XML_COLUMN RETURNING CONTENT) extract FROM mytab t</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjs8BRkpunxBDoo-ck8PbsbxPanJXT-hNzofbBBrn_EnggR1ZrGFf6TCTV8O9CkTGmDeNvStqPNDGj5VpTI1ov54c-oYJlhkD5oRQlJWAmE0EG9prMfOMkgGof0E1Nx7cFgj2py/s1600/xml18a.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="876" data-original-width="1214" height="460" id="id_de43_6f9d_9687_9b3e" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjs8BRkpunxBDoo-ck8PbsbxPanJXT-hNzofbBBrn_EnggR1ZrGFf6TCTV8O9CkTGmDeNvStqPNDGj5VpTI1ov54c-oYJlhkD5oRQlJWAmE0EG9prMfOMkgGof0E1Nx7cFgj2py/s640/xml18a.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<span style="font-family: "courier new" , "courier" , monospace;">SQL></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">SELECT rownum, t.*,</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> 2</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;"> </span><span style="font-family: "courier new" , "courier" , monospace;">XMLQuery('/alertlog/msg/txt' PASSING t.XML_COLUMN RETURNING CONTENT) extract FROM mytab t</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiO_pCVXQClA_UU4IWKQZ7vtbVcVqeicbL9VX6DL8k17u5cKJVUN9nkb270isXyYS9YSA6PNGKHX24ITNOLmi4DqrgHyklJ9fmPVL4BVsKxtLq5zmMKBQKOmSS1bxiuOnVUHiG1/s1600/xml19.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="870" data-original-width="1087" height="512" id="id_ef3c_e2de_c219_912" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiO_pCVXQClA_UU4IWKQZ7vtbVcVqeicbL9VX6DL8k17u5cKJVUN9nkb270isXyYS9YSA6PNGKHX24ITNOLmi4DqrgHyklJ9fmPVL4BVsKxtLq5zmMKBQKOmSS1bxiuOnVUHiG1/s640/xml19.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Here other two examples<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUUnnszlsiTJLFcKNFt7uysY29rYxFTVgO-7ZeFpu-T5DUnE56rFLvByI4R6721CSdDD5GgxtafbgjjEDihNUUNC12EBVAWTEFE0P3hJksn8duGXAmA-kEg795gGCzFlnyrB-U/s1600/xml20.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="895" data-original-width="1170" height="488" id="id_5443_a23c_4b19_230" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUUnnszlsiTJLFcKNFt7uysY29rYxFTVgO-7ZeFpu-T5DUnE56rFLvByI4R6721CSdDD5GgxtafbgjjEDihNUUNC12EBVAWTEFE0P3hJksn8duGXAmA-kEg795gGCzFlnyrB-U/s640/xml20.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBNHL9Y5DjyEbhDyWbeGg5HHlTL29bSP8Sy5-GfsaA4zY-NbEX2NrRfik-zCo0sh83zlYi_Cu377ymhhIB0BRaNtoKhhEH2opXrwD5Ppq2UCRXIc91Ifij50uEUu3C8P3PGbhS/s1600/xml21.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="911" data-original-width="1184" height="492" id="id_84de_c33f_3ef1_af0f" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBNHL9Y5DjyEbhDyWbeGg5HHlTL29bSP8Sy5-GfsaA4zY-NbEX2NrRfik-zCo0sh83zlYi_Cu377ymhhIB0BRaNtoKhhEH2opXrwD5Ppq2UCRXIc91Ifij50uEUu3C8P3PGbhS/s640/xml21.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
As you can see, also if there is a little difference in the syntax between XMLQuery and EXTRACT function, the outptu is the same. The XMLQuery function <i>returns the TAGs</i>. The same thing of the EXTRACT function.<br />
<br />
In the next post XMLTable<br />
<br />
<br />
References<br />
[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640<br />
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173<br />
[3] https://docs.oracle.com/database/122/SQLRF/XMLQUERY.htm#SQLRF06209<br />
[4] https://docs.oracle.com/database/122/SQLRF/XMLTABLE.htm#SQLRF06232<br />
[5] http://allthingsoracle.com/xquery-for-absolute-beginners-part-1-xpath/<br />
[6] http://allthingsoracle.com/xquery-for-absolute-beginners-part-2-xquery-and-the-oracle-database/<br />
[7] https://www.youtube.com/watch?v=n_dEb6myFH4<br />
[8] https://www.youtube.com/watch?v=5d4fZVxFx20<br />
[9] <a href="https://books.google.it/books?id=gUPWAQAAQBAJ&pg=PA481&lpg=PA481&dq=oracle+xmltable+vs+extractvalue&source=bl&ots=WZr_HVzMi7&sig=3G0Q7Mqvlr6RitkLBzg8-7Ap7-A&hl=it&sa=X&ved=0ahUKEwj3rMCQ_P7VAhVDvBQKHTYyDtE4FBDoAQhUMAY#v=onepage&q=oracle%20xmltable%20vs%20extractvalue&f=false">Pro Oracle SQL, pag 481 written by Morton, Osborne, Shamsudeen [Apress</a>]<br />
[10] http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf (<a href="https://drive.google.com/open?id=0B8fWOVWyjKLMcFFPRlo1TmtVM0U">PDF</a>)<br />
<div>
<br /></div>
<div>
<a href="http://orasal.blogspot.com/2017/08/using-xml-06-select-part-12.html" id="id_af78_588d_6fb8_dbea">Part06</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index </a><a href="http://orasal.blogspot.com/2017/09/using-xml-08-select-part-33a.html" id="id_f574_6f71_fba0_394e">Part08</a></div>
Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0tag:blogger.com,1999:blog-27589819.post-2391605067553297472017-08-30T12:04:00.000+02:002017-10-11T15:52:32.665+02:00Using XML 06: SELECT, Part 1/3In the last two posts [3][4], I was shown how to insert a record (an XML document) in an XML column. Here I want to show how to select data inside an XML column<br />
<br />
In order to SELECT data, you have to use some functions. Two of these are EXTRACT [1] and EXTRACTVALUE [2]<br />
<br />
<h3>
EXTRACT [1]</h3>
It applies to a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.<br />
<div>
<br /></div>
<span style="font-family: "courier new" , "courier" , monospace;">EXTRACT (<XMLType_instance</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">, </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XPATH_string</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<div>
<br /></div>
<div>
I start using just <span style="font-family: "courier new" , "courier" , monospace;">EXTRACT(XML_COLUMN, '/alertlog')</span></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijim8KO7qHjgV1h5QZnx0vmT42ZgDuvMJOqliZ1Z0EIzJiECkI7fpNNYCta5j7wI7oUfZVzubXKWfUjTa6Jbsic5T__vWi1EvTI3pfDAhj7cea8AUd5ksGgVFafwYtTysV7JcJ/s1600/xml16a.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="884" data-original-width="1079" height="524" id="id_f98e_7d82_d1db_2da" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijim8KO7qHjgV1h5QZnx0vmT42ZgDuvMJOqliZ1Z0EIzJiECkI7fpNNYCta5j7wI7oUfZVzubXKWfUjTa6Jbsic5T__vWi1EvTI3pfDAhj7cea8AUd5ksGgVFafwYtTysV7JcJ/s640/xml16a.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div>
<br /></div>
<div>
The result is the entire XML content. This is because I chosen the root node. So, what I expect if I use <span style="font-family: "courier new" , "courier" , monospace;">EXTRACT(XML_COLUMN, '/alertlog/msg') </span>is the EXTRACT column without the root node. And in fact:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibB9l1qlipR1tQeyliSIePplJCsVMjKWsZRMLv13zGDBUsRAv7qnOsUI-K2o2Ksuz5B33gblKUTzIg9GD0yDzmoLHKICm6fyUNK26LZtqSeKi4yDnHHukl2F-kJYirH3ug6wak/s1600/xml16.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="932" data-original-width="1102" height="540" id="id_869e_ddb6_dd26_8e47" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibB9l1qlipR1tQeyliSIePplJCsVMjKWsZRMLv13zGDBUsRAv7qnOsUI-K2o2Ksuz5B33gblKUTzIg9GD0yDzmoLHKICm6fyUNK26LZtqSeKi4yDnHHukl2F-kJYirH3ug6wak/s640/xml16.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
A similar things I expect if I use <span style="font-family: "courier new" , "courier" , monospace;">EXTRACT(XML_COLUMN, '/alertlog/msg/txt'). </span>I mean, the output should be only the "txt" element.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNSAOkkDPlw4f5P-1tkK_BPdvRZXtFOD-gDYz4m51tC9tK6wRECgpGdSSpB5DlOKhwzbyMsUN7ptlpSGtP_0UAMVk-yem24HvAEUOdfmiD11dM55tZZFBx7IY2bBxK4Cog9SRn/s1600/xml17.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="890" data-original-width="1096" height="518" id="id_86e2_f593_e3e8_876f" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNSAOkkDPlw4f5P-1tkK_BPdvRZXtFOD-gDYz4m51tC9tK6wRECgpGdSSpB5DlOKhwzbyMsUN7ptlpSGtP_0UAMVk-yem24HvAEUOdfmiD11dM55tZZFBx7IY2bBxK4Cog9SRn/s640/xml17.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<div>
<br /></div>
In all cases, only one row is returned (yes there is one row in the table).<br />
<br />
<h3>
<br />EXTRACTVALUE [2]</h3>
The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. <u>The result must be a single node and be either a text node, attribute, or element</u>. <u>If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns</u>. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">EXTRACTVALUE (<XMLType_instance</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">, </span><span style="font-family: "courier new" , "courier" , monospace;"><</span><span style="font-family: "courier new" , "courier" , monospace;">XPATH_string</span><span style="font-family: "courier new" , "courier" , monospace;">></span><span style="font-family: "courier new" , "courier" , monospace;">)</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhATroDqEVIuepdNb1eP54d_lCd9EDKFwUlRxEhEEvfONiFW-YdO4m6RSuBqWd7Wzb41gRcJDJssSXMmXWZQgp5Va8_396AOOhoaRN5wzJI684dAdOX6NDlRbmUFY04poDirGvR/s1600/xml22.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="392" data-original-width="858" height="292" id="id_4a1_d7b6_537e_d90f" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhATroDqEVIuepdNb1eP54d_lCd9EDKFwUlRxEhEEvfONiFW-YdO4m6RSuBqWd7Wzb41gRcJDJssSXMmXWZQgp5Va8_396AOOhoaRN5wzJI684dAdOX6NDlRbmUFY04poDirGvR/s640/xml22.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
I have to follow the manual above: <i>The result must be a single node and be either a text node, attribute, or element. </i><span style="-webkit-text-size-adjust: auto; background-color: rgba(255, 255, 255, 0);"><i>If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. </i>This is why I had previous exception. If I specify just an element, then...</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7tfHPywZi3SgYoTUu1yOd44-YcXcLFRVOLxWndChTGJ8AR-uGuyUtnKgLof-La0N-qZj-7jXzOlddDhON2fBdIrIjHtnRmIDmHdljMWEIz-vmk8GLbCUkIfzb9qCwVfrqzgsW/s1600/xml23.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="875" data-original-width="1078" height="518" id="id_ee2c_82f2_3694_8e36" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7tfHPywZi3SgYoTUu1yOd44-YcXcLFRVOLxWndChTGJ8AR-uGuyUtnKgLof-La0N-qZj-7jXzOlddDhON2fBdIrIjHtnRmIDmHdljMWEIz-vmk8GLbCUkIfzb9qCwVfrqzgsW/s640/xml23.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
Also this, is a valid statement<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi41NuydQEI9EN3ab8M657MK0UATnvdHffYM3d1fzx7ikPlOLBCzS5H_HsqvGZgwCPFLBSmHVfsz1a9SHnf-ul8prd7N0zRmSLafUawvXkMiZvPMWAIg2CCghi-NayASTq3pprg/s1600/xml24.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="873" data-original-width="1083" height="514" id="id_d6c4_f9f1_9e79_619e" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi41NuydQEI9EN3ab8M657MK0UATnvdHffYM3d1fzx7ikPlOLBCzS5H_HsqvGZgwCPFLBSmHVfsz1a9SHnf-ul8prd7N0zRmSLafUawvXkMiZvPMWAIg2CCghi-NayASTq3pprg/s640/xml24.JPG" style="height: auto; width: 640px;" width="640" /></a></div>
<br />
As you see in this case, only onw row is returned. Anyway, the documentation says:<br />
<br />
<i>The <u>EXTRACT (XML)</u> function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the <u>XMLQUERY</u> function instead. See XMLQUERY for more information</i> [1]<br />
<br />
<i>The <u>EXTRACTVALUE</u> function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the <u>XMLTABLE</u> function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information</i> [2]<br />
<br />
In the next post. we see how to use the XMLQuery function in order to obtain the same result<br />
<br />
<h3>
References</h3>
[1] https://docs.oracle.com/database/122/SQLRF/EXTRACT-XML.htm#SQLRF00640<br />
[2] https://docs.oracle.com/database/122/SQLRF/EXTRACTVALUE.htm#SQLRF06173<br />
[3] https://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table.html<br />
[4] https://orasal.blogspot.it/2017/08/using-xml-04-insert-into-table-part-22.html<br />
<div>
<br /></div>
<a href="http://orasal.blogspot.com/2017/08/using-xml-04-insert-into-table-part-22.html" id="id_9308_644e_eb87_4d2f" target="_self">Part05</a> <a href="http://orasal.blogspot.it/2017/09/using-xml-argument-index.html">Index</a> <a href="http://orasal.blogspot.com/2017/08/using-xml-07-select-part-23.html" id="id_6baa_18bd_2f4a_2ae8" target="_self">Part07</a>Andrea Salzanohttp://www.blogger.com/profile/07699475917994685383noreply@blogger.com0