Thursday, March 19, 2009

Choosing between SQL and PL/SQL

If you prefer to read in German, then you can find a translation here.


On my post Calculating probabilities with N throws of a die, I received a comment by Narendra saying:

I hope you are not serious about your last statement with some comments I'm sure it's not that hard to maintain....:)

But I was serious. However, it's a sentiment I hear a lot: after my presentation Do more with SQL I received similar comments claiming PL/SQL would be easier in some cases. And according to Iggy Fernandez, Steven Feuerstein has said here:

Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.

And I don't get it.

Well, I can understand why that would be a first reaction when seeing some undocumented long piece of SQL containing some of the newer SQL constructs. But when thinking a bit longer about the subject, I don't think it's a totally fair reaction. Here's why.

  1. When faced with a challenging problem, most people tend to resort to the language they are most comfortable with. I know I do. For example, I'm way better with PL/SQL than I am with Java. So when faced with a hard algorithm, I'll always use PL/SQL. And I bet a Java programmer reasons the other way round. So when saying that straight SQL is harder to maintain than PL/SQL, I guess you are really saying that your PL/SQL skills are very good, but your SQL skills are, well, somewhat less than very good. That's no problem at all, since you will still be able to build applications effectively. But I don't think the language itself is to blame, it's the skills of the people talking that language.


  2. In production code, I see PL/SQL code more often being documented with comments than SQL code. But every piece of production code that isn't straightforward should be documented. Why does SQL code rarely contain comments? Probably because most SQL statements in production are of the basic SELECT ... FROM ... WHERE kind. And when used to not commenting those easy SQL statements, the harder ones are almost automatically lacking comments as well. When you start adding comments for SQL code, as well as for your PL/SQL code, then it will be a reason less for why you may find SQL code harder to grasp.


  3. In PL/SQL you are able to split up a complex task into many simple tasks. Each task is then performed by a single function or a procedure. And those functions and procedures have clear names, making the code self documenting. This is called modularizing your code and you've probably been taught about this subject in school already. Complex SQL used to be just a giant piece of text lacking this ability, but from Oracle9i onwards, Oracle gave us the WITH-clause (also known as subquery factoring). With this clause you can give a meaningful name to each subpiece of SQL. Thus, the same kind of modularization we achieve with PL/SQL, is possible with SQL since 9i as well.

And so I think SQL is equally readable, just a little more compact. And often faster due to less context switching. And more often correct because it's one read consistent query instead of several queries taking place at different times.

An example using the code from Calculating probabilities with N throws of a die, of how it could look like in production. Difference is that the results of that query are inserted into a table. I made both variants "production like" by documenting them well. First a package using SQL:

rwijk@ORA11GR1> create package probabilities_sql
2 as
3 --
4 -- The procedure "calculate" calculates all probabilities with
5 -- p_number_of_dies throws of a die. The results of the calculation
6 -- are inserted into the table probabilities.
7 --
8 procedure calculate (p_number_of_throws in number)
9 ;
10 end probabilities_sql;
11 /

Package is aangemaakt.

rwijk@ORA11GR1> create package body probabilities_sql
2 as
3 procedure calculate (p_number_of_throws in number)
4 is
5 begin
6 insert into probabilities
7 ( sum_of_dies
8 , percentage
9 )
10 with number_of_die_faces as (select count(*) cnt from die)
11 , all_probabilities as
12 ( select sum_value
13 , prob
14 , i
15 from --
16 -- Generate as many rows as there are possible combinations of the
17 -- dies. This equals: power(,p_number_of_throws).
18 -- For example: with a traditional die (6 faces) and 3 throws, there
19 -- are power(6,3) = 216 rows with a l-value running from 1 until 216.
20 --
21 ( select level l
22 from number_of_die_faces
23 connect by level <= power(cnt,p_number_of_throws)
24 )
25 , number_of_die_faces
26 model
27 --
28 -- A reference model to be able to quickly lookup the face_value
29 -- and probability when provided a face_id
30 --
31 reference r on (select face_id, face_value, probability from die)
32 dimension by (face_id)
33 measures (face_value,probability)
34 main m
35 --
36 -- Each combination is in a different partition.
37 -- Which means it is easy to parallellize if necessary.
38 --
39 partition by (l rn, cnt)
40 dimension by (0 i)
41 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
42 --
43 -- Iterate as many times as there are throws of the die.
44 --
45 rules iterate (1000) until (iteration_number+1=p_number_of_throws)
46 --
47 -- For each throw of the die, calculate the face_id, remainder, the
48 -- sum and probability. For the sum and probability, the reference
49 -- model is used as a lookup. Each iteration overwrites the previous
50 -- one.
51 --
52 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
53 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
54 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
55 , prob[0] = prob[0] * probability[die_face_id[0]]
56 )
57 )
58 --
59 -- All probabilities of each possible combination are now calculated.
60 -- Now, sum them all up per sum of all face_values.
61 --
62 select sum_value
63 , sum(prob)
64 from all_probabilities
65 group by sum_value
66 ;
67 end calculate;
68 end probabilities_sql;
69 /

Package-body is aangemaakt.


And a package doing it the PL/SQL way, using the same idea:

rwijk@ORA11GR1> create package probabilities_plsql
2 as
3 --
4 -- The procedure "calculate" calculates all probabilities with
5 -- p_number_of_dies throws of a die. The results of the calculation
6 -- are inserted into the table probabilities.
7 --
8 procedure calculate (p_number_of_throws in number)
9 ;
10 end probabilities_plsql;
11 /

Package is aangemaakt.

rwijk@ORA11GR1> create package body probabilities_plsql
2 as
3 g_number_of_die_faces number(4)
4 ;
5 procedure initialization
6 --
7 -- Calculate the number of die faces (6 in case of a traditional die) only once.
8 --
9 is
10 begin
11 select count(*)
12 into g_number_of_die_faces
13 from die
14 ;
15 end initialization
16 ;
17 function face_value
18 ( p_face_id in die.face_id%type
19 ) return die.face_value%type result_cache relies_on (die)
20 --
21 -- A lookup function returning the face_value of a given face_id.
22 -- This function is called multiple times for the same face_id's and
23 -- is therefore optimized by the result_cache hint.
24 --
25 is
26 l_face_value die.face_value%type;
27 begin
28 select face_value
29 into l_face_value
30 from die
31 where face_id = p_face_id
32 ;
33 return l_face_value;
34 end face_value
35 ;
36 function probability
37 ( p_face_id in die.face_id%type
38 ) return die.probability%type result_cache relies_on (die)
39 --
40 -- A lookup function returning the probability of a given face_id.
41 -- This function is called multiple times for the same face_id's and
42 -- is therefore optimized by the result_cache hint.
43 --
44 is
45 l_probability die.probability%type;
46 begin
47 select probability
48 into l_probability
49 from die
50 where face_id = p_face_id
51 ;
52 return l_probability;
53 end probability
54 ;
55 procedure calculate (p_number_of_throws in number)
56 is
57 l_die_face_id die.face_id%type;
58 l_remainder number(10);
59 l_sum probabilities.sum_of_dies%type;
60 l_probability probabilities.percentage%type
61 ;
62 type ta_probabilities is table of probabilities%rowtype index by pls_integer;
63 a_probabilities ta_probabilities;
64 begin
65 --
66 -- Loop as many times as there are possible combinations of the
67 -- dies. This number equals: power(,p_number_of_throws).
68 -- For example: with a traditional die (6 faces) and 3 throws, there
69 -- are power(6,3) = 216 iterations.
70 --
71 for i in 1 .. power(g_number_of_die_faces,p_number_of_throws)
72 loop
73 l_remainder := i;
74 l_sum := 0;
75 l_probability := 1;
76 --
77 -- For each combination, iterate over all throws of each individual die,
78 -- and calculate the face_id of that die (using l_die_face_id and
79 -- l_remainder) and use that face_id to calculate the sum of the die
80 -- face values and the probability.
81 --
82 for j in 1 .. p_number_of_throws
83 loop
84 l_die_face_id := 1 + mod(l_remainder-1, g_number_of_die_faces);
85 l_remainder := ceil((l_remainder-l_die_face_id+1)/g_number_of_die_faces);
86 l_sum := l_sum + face_value(l_die_face_id);
87 l_probability := l_probability * probability(l_die_face_id);
88 end loop;
89 --
90 -- Sum up all the probabilities with the same sum.
91 --
92 a_probabilities(l_sum).sum_of_dies := l_sum;
93 a_probabilities(l_sum).percentage :=
94 nvl(a_probabilities(l_sum).percentage,0) + l_probability
95 ;
96 end loop;
97 --
98 -- Bulk insert all calculated probabilities into the table PROBABILIIES.
99 --
100 forall i in indices of a_probabilities
101 insert into probabilities
102 values a_probabilities(i)
103 ;
104 end calculate
105 ;
106 begin
107 initialization;
108 end probabilities_plsql;
109 /

Package-body is aangemaakt.


Note that this algorithm is not data intensive and the PL/SQL variant here is actually faster than the SQL variant due to 11g's result cache, but that's not the point here. The point is of course readability. Do you really think the SQL variant is much more complex than the PL/SQL variant? I'd love to hear your thoughts about this subject, whether you agree or not.



UPDATE



Three nice follow-up blog entries:

by Chen Shapira

by Laurent Schneider

by H.Tonguç Yılmaz

Monday, March 16, 2009

The Helsinki Declaration

Toon Koppelaars started blogging "About how database web application development nowadays has gone absolutely and utterly absurd". I cannot agree more. If you want to read why, then you should definitely add this blog to your reader.

Monday, March 9, 2009

Presentation about grouping

At May 26 I will be doing a presentation at Planboard's Oracle DBA Symposium. The presentation is about grouping in SQL and more specific about rollups, cubes and grouping sets and how they work on the outside and on the inside.

Even though the symposium is aimed at DBA's, I see several interesting presentations for me as a database developer as well. I'm looking forward to those presentations and I am keeping my fingers crossed that they won't be at the same time as my own ...

Calculating probabilities with N throws of a die

A translation of this post in German can be found here.


Chen Shapira pointed me to a SQL riddle by Iggy Fernandez. Laurent Schneider already gave an impressively simple solution using a hierarchical query and the XMLQuery function. I used the SQL model clause (of course) to achieve the same:

rwijk@ORA11GR1> select * from die order by face_id
2 /

FACE_ID FACE_VALUE PROBABILITY
---------- ---------- -----------
1 1 .25
2 3 .25
3 4 .25
4 5 .083333333
5 6 .083333333
6 8 .083333333

6 rows selected.

rwijk@ORA11GR1> var N number
rwijk@ORA11GR1> exec :N := 2

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
2 .0625
4 .125
5 .125
6 .104166667
7 .166666667
8 .104166667
9 .125
10 .048611111
11 .055555556
12 .048611111
13 .013888889
14 .013888889
16 .006944444
1

14 rows selected.

rwijk@ORA11GR1> exec :N := 3

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
3 .015625
5 .046875
6 .046875
7 .0625
8 .109375
9 .09375
10 .125
11 .098958333
12 .104166667
13 .088541667
14 .057291667
15 .05787037
16 .032986111
17 .027777778
18 .012731481
19 .008680556
20 .006944444
21 .001736111
22 .001736111
24 .000578704
1

21 rows selected.


And with a traditional die:

rwijk@ORA11GR1> update die
2 set face_value = face_id
3 , probability = 1/6
4 /

6 rows updated.

rwijk@ORA11GR1> select * from die order by face_id
2 /

FACE_ID FACE_VALUE PROBABILITY
---------- ---------- -----------
1 1 .166666667
2 2 .166666667
3 3 .166666667
4 4 .166666667
5 5 .166666667
6 6 .166666667

6 rows selected.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
3 .00462963
4 .013888889
5 .027777778
6 .046296296
7 .069444444
8 .097222222
9 .115740741
10 .125
11 .125
12 .115740741
13 .097222222
14 .069444444
15 .046296296
16 .027777778
17 .013888889
18 .00462963
1

17 rows selected.

rwijk@ORA11GR1> exec :N := 4

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
4 .000771605
5 .00308642
6 .007716049
7 .015432099
8 .027006173
9 .043209877
10 .061728395
11 .080246914
12 .096450617
13 .108024691
14 .112654321
15 .108024691
16 .096450617
17 .080246914
18 .061728395
19 .043209877
20 .027006173
21 .015432099
22 .007716049
23 .00308642
24 .000771605
1

22 rows selected.


About the solution: it generates as much rows as there are combinations (power(cnt,:N)). With the die_face_id and remainder measures, each generated row is converted to N face_id's in N iterations. The face_value and probability numbers are looked up using a reference model. Finally, the outer query just sums up all probabilities.

The SQL language is "not complete", but since version 10 I seriously wonder if there are problems out there that cannot be solved using SQL. Although it may look like a pretzel to some, with some comments I'm sure it's not that hard to maintain.

Sunday, March 1, 2009

Pulling XML out of the database

To have an XMLType variable in your PL/SQL program, and filled with database content, I almost exclusively use the XML publishing functions like XMLAgg, XMLElement and XMLForest. But there are more options. You can use the dbms_xmlgen package for this purpose. And via a colleague who attended an AMIS session by Matthieu de Graaf early this year, I became aware of a third option. The XMLType constructor is heavily overloaded in its first argument, and one of the many possibilities is a SYS_REFCURSOR variable. See the documentation here. This opens up new opportunities. And searching a little more, I also found a fourth way using stylesheets and XMLTransform. Below I will give an example of those four methods, for easy referencing.

First a simple example: selecting two columns from a single table. Using the XML publishing functions:

rwijk@ORA11GR1> declare
2 l_xml_as_clob clob;
3 begin
4 select xmlserialize
5 ( document xmlroot
6 ( xmlelement
7 ( "Departments"
8 , xmlagg
9 ( xmlelement
10 ( "Department"
11 , xmlforest
12 ( deptno as "Number"
13 , dname as "Name"
14 )
15 )
16 )
17 )
18 ) as clob indent
19 )
20 into l_xml_as_clob
21 from dept
22 where deptno in (10,20)
23 ;
24 dbms_output.put_line(l_xml_as_clob);
25 end;
26 /
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The second variant using dbms_xmlgen, works likes this:

rwijk@ORA11GR1> declare
2 l_xml xmltype;
3 l_context_handle dbms_xmlgen.ctxHandle;
4 begin
5 l_context_handle := dbms_xmlgen.newContext
6 ( 'select d.deptno "Number"
7 , d.dname "Name"
8 from dept d
9 where d.deptno in (10,20)'
10 );
11 dbms_xmlgen.setRowSetTag(l_context_handle, 'Departments');
12 dbms_xmlgen.setRowTag(l_context_handle, 'Department');
13 l_xml := xmltype(dbms_xmlgen.getXML(l_context_handle));
14 dbms_xmlgen.closeContext(l_context_handle);
15 dbms_output.put_line(l_xml.getClobVal());
16 end;
17 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>


PL/SQL procedure successfully completed.


Note that the default ROWSET and ROW tagnames are replaced using the setRowTag and setRowSetTag procedures, by the more meaningful names Departments and Department.

The third variant using the XMLType constructor that accepts a ref cursor:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select deptno "Number"
8 , dname "Name"
9 from dept
10 where deptno in (10,20)
11 ;
12 l_xml := xmltype(l_rc);
13 close l_rc;
14 dbms_output.put_line(l_xml.getClobVal());
15 end;
16 /
<?xml version="1.0"?>
<ROWSET>
<ROW>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</ROW>
<ROW>
<Number>20</Number>
<Name>RESEARCH</Name>
</ROW>
</ROWSET>


PL/SQL procedure successfully completed.


Using the XML publishing functions you are free to use any tagname you want, but here we get standard names without nice functions to change the tagnames. We can use some aliases to get more tagnames right, but the ROWSET and ROW ones stay. There seems to be nothing else left than to use good old - and rather ugly - replace functions to get the same tagnames as in the first and second example:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select deptno "Number"
8 , dname "Name"
9 from dept
10 where deptno in (10,20)
11 ;
12 l_xml := xmltype(l_rc);
13 close l_rc;
14 dbms_output.put_line
15 ( replace
16 ( replace
17 ( replace
18 ( replace
19 ( l_xml.getClobVal()
20 , '<ROWSET>'
21 , '<Departments>'
22 )
23 , '</ROWSET>'
24 , '</Departments>'
25 )
26 , '<ROW>'
27 , '<Department>'
28 )
29 , '</ROW>'
30 , '</Department>'
31 )
32 );
33 end;
34 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The fourth option is to use XMLTransform and a stylesheet, like this:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 select xmlroot
6 ( xmltransform
7 ( xmltype
8 ( cursor
9 ( select d.deptno
10 , d.dname
11 from dept d
12 where d.deptno in (10,20)
13 )
14 )
15 , xmltype.createxml
16 ( '<?xml version="1.0"?>
17 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
18 <xsl:template match="/">
19 <Departments>
20 <xsl:for-each select="/ROWSET/ROW">
21 <Department>
22 <Number><xsl:value-of select="DEPTNO"/></Number>
23 <Name><xsl:value-of select="DNAME"/></Name>
24 </Department>
25 </xsl:for-each>
26 </Departments>
27 </xsl:template>
28 </xsl:stylesheet>'
29 )
30 )
31 )
32 into l_xml
33 from dual
34 ;
35 dbms_output.put_line(l_xml.getClobVal());
36 end;
37 /
<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>

PL/SQL procedure successfully completed.


So far, it's quite easy, because the query was easy. Now let's try to add some extras: a list of employees with their name and salary for each department. First with the XML publishing functions:

rwijk@ORA11GR1> declare
2 l_xml_as_clob clob;
3 begin
4 select xmlserialize
5 ( document xmlroot
6 ( xmlelement
7 ( "Departments"
8 , xmlagg
9 ( xmlelement
10 ( "Department"
11 , xmlforest
12 ( d.deptno as "Number"
13 , d.dname as "Name"
14 )
15 , xmlelement
16 ( "Employees"
17 , xmlagg
18 ( xmlelement
19 ( "Employee"
20 , xmlforest
21 ( e.ename as "Name"
22 , e.sal as "Salary"
23 )
24 )
25 )
26 )
27 )
28 )
29 )
30 ) as clob indent
31 )
32 into l_xml_as_clob
33 from dept d
34 , emp e
35 where d.deptno = e.deptno
36 and d.deptno in (10,20)
37 group by d.deptno
38 , d.dname
39 ;
40 dbms_output.put_line(l_xml_as_clob);
41 end;
42 /
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
</Employees>
</Department>
</Departments>


PL/SQL procedure successfully completed.


As you can see, the select list becomes increasingly complex. But once you manage to pull off the simple variant, you will likely succeed in more complex expressions as well.

Using dbms_xmlgen, it may not be immediately obvious how to handle such a master-detail type of datastructure in a single query. But my first try using cursor expressions was successful:

rwijk@ORA11GR1> declare
2 l_xml xmltype;
3 l_context_handle dbms_xmlgen.ctxHandle;
4 begin
5 l_context_handle := dbms_xmlgen.newContext
6 ( 'select d.deptno "Number"
7 , d.dname "Name"
8 , cursor
9 ( select e.ename "Name"
10 , e.sal "Salary"
11 from emp e
12 where e.deptno = d.deptno
13 ) "Employees"
14 from dept d
15 where d.deptno in (10,20)'
16 );
17 dbms_xmlgen.setRowSetTag(l_context_handle, 'Departments');
18 dbms_xmlgen.setRowTag(l_context_handle, 'Department');
19 l_xml := xmltype(dbms_xmlgen.getXML(l_context_handle));
20 dbms_xmlgen.closeContext(l_context_handle);
21 dbms_output.put_line
22 ( replace
23 ( replace
24 ( l_xml.getClobVal()
25 , '<Employees_ROW>'
26 , '<Employee>'
27 )
28 , '</Employees_ROW>'
29 , '</Employee>'
30 )
31 );
32 end;
33 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The only tag that needs to be adjusted with replace functions, is the "Employees_ROW" tag. By the way, be sure to give the cursor expression an alias, or you'll end up with horrible tagnames!

For the XMLType constructor using a sys_refcursor, it works almost the same:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select d.deptno "Number"
8 , d.dname "Name"
9 , cursor
10 ( select e.ename "Name"
11 , e.sal "Salary"
12 from emp e
13 where e.deptno = d.deptno
14 ) "Employees"
15 from dept d
16 where d.deptno in (10,20)
17 ;
18 l_xml := xmltype(l_rc);
19 close l_rc;
20 dbms_output.put_line
21 ( replace
22 ( replace
23 ( replace
24 ( replace
25 ( replace
26 ( replace
27 ( l_xml.getClobVal()
28 , '<Employees_ROW>'
29 , '<Employee>'
30 )
31 , '</Employees_ROW>'
32 , '</Employee>'
33 )
34 , '<ROWSET>'
35 , '<Departments>'
36 )
37 , '</ROWSET>'
38 , '</Departments>'
39 )
40 , '<ROW>'
41 , '<Department>'
42 )
43 , '</ROW>'
44 , '</Department>'
45 )
46 );
47 end;
48 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The query and especially the select list, is much cleaner than the one using the XML publishing functions, but the replace functions are still very clumsy.

I cannot get the last alternative using XMLTransform and stylesheets to work. In SQL it doesn't give an error message:

rwijk@ORA11GR1> select xmlroot
2 ( xmltransform
3 ( xmltype
4 ( cursor
5 ( select deptno
6 , dname
7 , cursor
8 ( select ename
9 , sal
10 from emp
11 where emp.deptno = dept.deptno
12 ) emps
13 from dept
14 where deptno in (10,20)
15 )
16 )
17 , xmltype.createxml
18 ( '<?xml version="1.0"?>
19 <xsl:stylesheet version="1.0"
20 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
21 <xsl:template match="/">
22 <Departments>
23 <xsl:for-each select="/ROWSET/ROW">
24 <Department>
25 <Number><xsl:value-of select="DEPTNO"/></Number>
26 <Name><xsl:value-of select="DNAME"/></Name>
27 <Employees>
28 <xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
29 <Employee>
30 <Name><xsl:value-of select="ENAME"/></Name>
31 <Salary><xsl:value-of select="SAL"/></Salary>
32 </Employee>
33 </xsl:for-each>
34 </Employees>
35 </Department>
36 </xsl:for-each>
37 </Departments>
38 </xsl:template>
39 </xsl:stylesheet>'
40 )
41 )
42 ) xml
43 from dual
44 /

XML
----------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


1 row selected.


But if you have a closer look at the employees per department, then you'll see every employee now listed in both departments 10 and 20. It seems impossible to correlate correctly using this method. We have just asked the stylesheet to collect all employees and list them all for each department.

Another strange phenomenon: putting this exact SQL statement inside a PL/SQL block raises an error:

rwijk@ORA11GR1> declare
2 l_xml xmltype;
3 begin
4 select xmlroot
5 ( xmltransform
6 ( xmltype
7 ( cursor
8 ( select deptno
9 , dname
10 , cursor
11 ( select ename
12 , sal
13 from emp
14 where emp.deptno = dept.deptno
15 ) emps
16 from dept
17 where deptno in (10,20)
18 )
19 )
20 , xmltype.createxml
21 ( '<?xml version="1.0"?>
22 <xsl:stylesheet version="1.0"
23 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
24 <xsl:template match="/">
25 <Departments>
26 <xsl:for-each select="/ROWSET/ROW">
27 <Department>
28 <Number><xsl:value-of select="DEPTNO"/></Number>
29 <Name><xsl:value-of select="DNAME"/></Name>
30 <Employees>
31 <xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
32 <Employee>
33 <Name><xsl:value-of select="ENAME"/></Name>
34 <Salary><xsl:value-of select="SAL"/></Salary>
35 </Employee>
36 </xsl:for-each>
37 </Employees>
38 </Department>
39 </xsl:for-each>
40 </Departments>
41 </xsl:template>
42 </xsl:stylesheet>'
43 )
44 )
45 )
46 into l_xml
47 from dual
48 ;
49 dbms_output.put_line(l_xml.getClobVal());
50 end;
51 /
declare
*
ERROR at line 1:
ORA-22916: cannot do an exact FETCH on a query with Nested cursors
ORA-06512: at line 4


First extracting the XML including the nested cursors, and then applying the stylesheet, leads to another problem:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select d.deptno
8 , d.dname
9 , cursor
10 ( select e.ename
11 , e.sal
12 from emp e
13 where e.deptno = d.deptno
14 ) emps
15 from dept d
16 where d.deptno in (10,20)
17 ;
18 l_xml := xmltype(l_rc);
19 close l_rc;
20 open l_rc
21 for
22 select xmltransform
23 ( l_xml
24 , xmltype.createxml
25 ( '<:?xml version="1.0"?>
26 <:xsl:stylesheet version="1.0"
27 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
28 <:xsl:template match="/">
29 <:Departments>
30 <:xsl:for-each select="/ROWSET/ROW">
31 <:Department>
32 <:Number><:xsl:value-of select="DEPTNO"/><:/Number>
33 <:Name><:xsl:value-of select="DNAME"/><:/Name>
34 <:Employees>
35 <:xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
36 <:Employee>
37 <:Name><:xsl:value-of select="ENAME"/><:/Name>
38 <:Salary><:xsl:value-of select="SAL"/><:/Salary>
39 <:/Employee>
40 <:/xsl:for-each>
41 <:/Employees>
42 <:/Department>
43 <:/xsl:for-each>
44 <:/Departments>
45 <:/xsl:template>
46 <:/xsl:stylesheet>'
47 )
48 )
49 from dual
50 ;
51 l_xml := xmltype(l_rc);
52 close l_rc;
53 dbms_output.put_line(l_xml.getClobVal());
54 end;
55 /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00110: Warning: invalid QName "XMLTRANSFORM_x0028_:B1_x002C_XMLTYPE.CREATEXML_x0
028__x0027__x003C__x003F_XMLVERSION_x003D__x0022_1.0_x0022__x003F__x003E__x003C_XSL:
STYLESHEETVERSION_x003D__x0022_1.0_x0022_XMLNS:XSL_x003D__x0022_HTTP:_x002F__x002F_.
..
Error at line 4
ORA-06512: at "SYS.XMLTYPE", line 343
ORA-06512: at line 51


This path is getting worse every step. The stylesheets have been an interesting journey, but there are at least three far less cumbersome alternatives to choose from.



UPDATE


With the help of Jason (see comments section below), I've got the fourth variant using stylesheet transformation working for both SQL and PL/SQL. In SQL the results become correlated again by specifying a path in the inner "for-each select" within the path of the outer "for-each select":

rwijk@ORA11GR1> select xmlroot
2 ( xmltransform
3 ( xmltype
4 ( cursor
5 ( select deptno
6 , dname
7 , cursor
8 ( select ename
9 , sal
10 from emp
11 where emp.deptno = dept.deptno
12 ) emps
13 from dept
14 where deptno in (10,20)
15 )
16 )
17 , xmltype.createxml
18 ( '<?xml version="1.0"?>
19 <xsl:stylesheet version="1.0"
20 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
21 <xsl:template match="/">
22 <Departments>
23 <xsl:for-each select="/ROWSET/ROW">
24 <Department>
25 <Number><xsl:value-of select="DEPTNO"/></Number>
26 <Name><xsl:value-of select="DNAME"/></Name>
27 <Employees>
28 <xsl:for-each select="EMPS/EMPS_ROW">
29 <Employee>
30 <Name><xsl:value-of select="ENAME"/></Name>
31 <Salary><xsl:value-of select="SAL"/></Salary>
32 </Employee>
33 </xsl:for-each>
34 </Employees>
35 </Department>
36 </xsl:for-each>
37 </Departments>
38 </xsl:template>
39 </xsl:stylesheet>'
40 )
41 )
42 ) xml
43 from dual
44 /

XML
-------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


1 rij is geselecteerd.


In PL/SQL you have to separate the stylesheet transformation from the XML-izing of the data and use XMLType() instead of the createxml-method:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select d.deptno
8 , d.dname
9 , cursor
10 ( select e.ename
11 , e.sal
12 from emp e
13 where e.deptno = d.deptno
14 ) emps
15 from dept d
16 where d.deptno in (10,20)
17 ;
18 l_xml := xmltype(l_rc);
19 close l_rc
20 ;
21 select xmlroot
22 ( xmltransform
23 ( l_xml
24 , xmltype
25 ( '<?xml version="1.0"?>
26 <xsl:stylesheet version="1.0"
27 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
28 <xsl:template match="/">
29 <Departments>
30 <xsl:for-each select="/ROWSET/ROW">
31 <Department>
32 <Number><xsl:value-of select="DEPTNO"/></Number>
33 <Name><xsl:value-of select="DNAME"/></Name>
34 <Employees>
35 <xsl:for-each select="EMPS/EMPS_ROW">
36 <Employee>
37 <Name><xsl:value-of select="ENAME"/></Name>
38 <Salary><xsl:value-of select="SAL"/></Salary>
39 </Employee>
40 </xsl:for-each>
41 </Employees>
42 </Department>
43 </xsl:for-each>
44 </Departments>
45 </xsl:template>
46 </xsl:stylesheet>'
47 )
48 )
49 )
50 into l_xml
51 from dual
52 ;
53 dbms_output.put_line(l_xml.getClobVal());
54 end;
55 /
<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>

PL/SQL-procedure is geslaagd.