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.

Tuesday, February 10, 2009

What's in my JMS queue?

If you have setup a JMS queue with Oracle's Advanced Queuing, a question that pops up regularly is: "What's currently in the queue?". Now that can't be hard - and it isn't - yet I spent a silly amount of time getting it done. Hopefully this blog post will prevent others spending too much time.

To setup the JMS queue, create a queue table with payload type sys.aq$_jms_text_message, and a queue in that queue table:

rwijk@ORA11GR1> begin
2 dbms_aqadm.create_queue_table
3 ( queue_table => 'my_queue_table'
4 , queue_payload_type => 'sys.aq$_jms_text_message'
5 );
6 dbms_aqadm.create_queue
7 ( queue_name => 'my_queue'
8 , queue_table => 'my_queue_table'
9 );
10 dbms_aqadm.start_queue
11 ( queue_name => 'my_queue'
12 );
13 end;
14 /

PL/SQL procedure successfully completed.


Next, put a message on the queue:

rwijk@ORA11GR1> declare
2 l_enqueue_options dbms_aq.enqueue_options_t;
3 l_message_properties dbms_aq.message_properties_t;
4 l_message sys.aq$_jms_text_message;
5 l_msgid raw(16);
6 begin
7 l_message := sys.aq$_jms_text_message.construct;
8 l_message.set_text(xmltype('<emp><ename>ROB</ename></emp>').getClobVal());
9 dbms_aq.enqueue
10 ( queue_name => 'my_queue'
11 , enqueue_options => l_enqueue_options
12 , message_properties => l_message_properties
13 , payload => l_message
14 , msgid => l_msgid
15 );
16 commit;
17 end;
18 /

PL/SQL procedure successfully completed.


You can query a queue by querying the queue table and filtering on the q_name column, like this:

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , user_data
5 from my_queue_table
6 where q_name = 'MY_QUEUE'
7 /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA(HEADER(REPLYTO(NAME, ADDRESS, PROTOCOL), TYPE, USERID, APPID, GROUPID,
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
AQ$_JMS_TEXT_MESSAGE(AQ$_JMS_HEADER(NULL, NULL, NULL, NULL, NULL, NULL, NULL), 2
9, '<emp><ename>ROB</ename></emp>', NULL)


1 row selected.


At work I use an older SQL*Plus version, that is not able to show the contents of the user_data column, because of some LOB content. No problem, as I am really only interested in the text_vc field of the sys.aq$_jms_text_message object. So just query that field:

rwijk@ORA11GR1> desc my_queue_table
Name Null? Type
----------------------------------------- -------- ------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
CORRID VARCHAR2(128)
PRIORITY NUMBER
STATE NUMBER
DELAY TIMESTAMP(6)
EXPIRATION NUMBER
TIME_MANAGER_INFO TIMESTAMP(6)
LOCAL_ORDER_NO NUMBER
CHAIN_NO NUMBER
CSCN NUMBER
DSCN NUMBER
ENQ_TIME TIMESTAMP(6)
ENQ_UID VARCHAR2(30)
ENQ_TID VARCHAR2(30)
DEQ_TIME TIMESTAMP(6)
DEQ_UID VARCHAR2(30)
DEQ_TID VARCHAR2(30)
RETRY_COUNT NUMBER
EXCEPTION_QSCHEMA VARCHAR2(30)
EXCEPTION_QUEUE VARCHAR2(30)
STEP_NO NUMBER
RECIPIENT_KEY NUMBER
DEQUEUE_MSGID RAW(16)
SENDER_NAME VARCHAR2(30)
SENDER_ADDRESS VARCHAR2(1024)
SENDER_PROTOCOL NUMBER
USER_DATA SYS.AQ$_JMS_TEXT_MESSAGE
USER_PROP SYS.ANYDATA

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , user_data.text_vc
5 from my_queue_table
6 where q_name = 'MY_QUEUE'
7 /
, user_data.text_vc
*
ERROR at line 4:
ORA-00904: "USER_DATA"."TEXT_VC": invalid identifier


And this message puzzled me for a quite a while. After an extensive search, an AskTom thread provided the answer. A very simple answer: you need to prefix the expression with a table alias:

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , qt.user_data.text_vc
5 from my_queue_table qt
6 where q_name = 'MY_QUEUE'
7 /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA.TEXT_VC
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
<emp><ename>ROB</ename></emp>


1 row selected.

Saturday, February 7, 2009

FOR UPDATE SKIP LOCKED

The behaviour of the "for update skip locked" clause is not what I expected it to be. I expected it to try to lock all rows when opening a cursor, just like a regular "for update", and then just skipping the locked rows. Below is a test to show how it works in reality. The setup:

A table with three rows:

rwijk@ORA11GR1> create table t (id)
2 as
3 select level
4 from dual
5 connect by level <= 3
6 /

Tabel is aangemaakt.


A procedure to check if a record of this table t is locked:

rwijk@ORA11GR1> create procedure check_record_locked
2 ( p_id in t.id%type
3 )
4 is
5 cursor c
6 is
7 select 'dummy'
8 from t
9 where id = p_id
10 for update nowait
11 ;
12 e_resource_busy exception;
13 pragma exception_init(e_resource_busy,-54);
14 begin
15 open c;
16 close c;
17 dbms_output.put_line('Record ' || to_char(p_id) || ' is not locked.');
18 rollback;
19 exception
20 when e_resource_busy then
21 dbms_output.put_line('Record ' || to_char(p_id) || ' is locked.');
22 end check_record_locked;
23 /

Procedure is aangemaakt.


First, let's examine the default behaviour of the regular "for update" clause. During the 10 seconds sleep, I copy, paste and execute the code in the comments in another SQL*Plus session.

rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update;
3 l_id t.id%type;
4 begin
5 open c;
6 fetch c into l_id;
7 dbms_lock.sleep(10);
8 --
9 -- In another session run:
10 --
11 -- begin
12 -- check_record_locked(1);
13 -- check_record_locked(2);
14 -- check_record_locked(3);
15 -- end;
16 -- /
17 --
18 close c;
19 end;
20 /

PL/SQL-procedure is geslaagd.


The output from the other session:

rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 end;
6 /
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.


As expected, the opening of the cursor locks rows 1 and 2. Whether the record is fetched or not, has no influence.

Now a similar test with the "for update skip locked" clause:

rwijk@ORA11GR1> rollback
2 /

Rollback is voltooid.

rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update skip locked;
3 l_id t.id%type;
4 begin
5 open c;
6 dbms_lock.sleep(10);
7 --
8 -- In another session run:
9 --
10 -- begin
11 -- check_record_locked(1);
12 -- check_record_locked(2);
13 -- check_record_locked(3);
14 -- dbms_lock.sleep(10);
15 -- check_record_locked(1);
16 -- check_record_locked(2);
17 -- check_record_locked(3);
18 -- dbms_lock.sleep(10);
19 -- check_record_locked(1);
20 -- check_record_locked(2);
21 -- check_record_locked(3);
22 -- end;
23 -- /
24 --
25 fetch c into l_id;
26 dbms_lock.sleep(10);
27 fetch c into l_id;
28 dbms_lock.sleep(10);
29 close c;
30 end;
31 /

PL/SQL-procedure is geslaagd.


During the first sleep of 10 seconds, the code in the comments is again copied, pasted and executed in a second SQL*Plus session. Note that it prints the situation after just having opened the cursor, after the first fetch and after the second fetch. This is the output:

rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 dbms_lock.sleep(10);
6 check_record_locked(1);
7 check_record_locked(2);
8 check_record_locked(3);
9 dbms_lock.sleep(10);
10 check_record_locked(1);
11 check_record_locked(2);
12 check_record_locked(3);
13 end;
14 /
Record 1 is not locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.


So opening a cursor with the "for update skip locked" clause doesn't lock a single row. Fetching a row does lock the row, as can be seen in the output. This was the first surprise: I couldn't find text in the documentation telling about this behaviour.

But won't this behaviour lead to a giant "lost update" problem? When you change a record in a second session between the open and the fetch of the cursor - which is allowed because the record is not locked - the cursor could overwrite this change because it has retrieved the value from before the update. Let's check how this works. The setup is a table with bank accounts containing 5 records with an identifying number, an amount and an indicator telling whether interest has been calculated already:

rwijk@ORA11GR1> create table bankaccounts(nr,amount,interest_calculated_indicator)
2 as
3 select level
4 , 100
5 , 'N'
6 from dual
7 connect by level <= 5
8 /

Tabel is aangemaakt.


First a test using the "for update" clause. A piece of code that calculates interest by multiplying the amount of all bankaccounts with the indicator set to 'N' by 5%. During the sleep of 10 seconds, the commented code is copied, pasted and executed in a second SQL*Plus session:

rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /

PL/SQL-procedure is geslaagd.


The second session has to wait with the update. So all amounts are set to 105.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 105 Y
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.


After this commit, the second session continues and produces this output:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 205 Y
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.


Everything as expected. But now the same code using a "for update skip locked". First restore the situation to its initial state:

rwijk@ORA11GR1> remark  Restore situation
rwijk@ORA11GR1> update bankaccounts
2 set amount = 100
3 , interest_calculated_indicator = 'N'
4 /

5 rijen zijn bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.


And execute the same code with a "for update skip locked":

rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update skip locked
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /

PL/SQL-procedure is geslaagd.


The second session doesn't have to wait, and the immediate response in this second SQL*Plus session is:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 100 N
2 200 N
3 100 N
4 100 N
5 100 N

5 rijen zijn geselecteerd.


Now I thought the output in the first session would show a lost update: the amount in record 2 being 105. But it didn't:

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 200 N
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.


It just didn't process the second row. So a "for update skip locked" doesn't only skip the locked rows, it also skips the rows that were modified between the opening and the fetching, thus avoiding the classic "lost update".

Thanks to James Padfield for hinting at this behaviour.

Friday, January 30, 2009

COUNT DISTINCT

This morning I saw an e-mail with the title "SQL puzzle?" in my inbox. That's a good title to make me read that one first. The poster was stunned by the results of the following two queries:

SQL> SELECT COUNT(DISTINCT bdl.nrl_id)
2 FROM a_table bdl
3 WHERE bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')
4 ;

COUNT(DISTINCTBDL.NRL_ID)
-------------------------
61111

1 rij is geselecteerd.

SQL> SELECT COUNT(*)
2 FROM (SELECT DISTINCT bdl.nrl_id
3 FROM a_table bdl
4 WHERE bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')
5 )
6 ;

COUNT(*)
----------
61112

1 rij is geselecteerd.


Of course it's possible that a record was inserted in the meantime, but that was not the case here.

The explanation was quite simple. See this example:

rwijk@ORA11GR1> select comm from emp
2 /

COMM
----------

300
500

1400




0





14 rijen zijn geselecteerd.

rwijk@ORA11GR1> select distinct comm from emp
2 /

COMM
----------

1400
500
300
0

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> select count(*) from (select distinct comm from emp)
2 /

COUNT(*)
----------
5

1 rij is geselecteerd.

rwijk@ORA11GR1> select count(distinct comm) from emp
2 /

COUNT(DISTINCTCOMM)
-------------------
4

1 rij is geselecteerd.


Again those pesky null values. From the documentation:
"All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls."
A distinct doesn't ignore nulls, and the count(*) on top of it doesn't either.

At first glance, a "select count(distinct ...)" appears equal to a "select count(*) from (select distinct ...)", but as shown: it is not.



UPDATE


I forgot to mention that a "select count(comm) from (select distinct comm...)" [note: count(comm) instead of count(*)] is equal to a "select count(distinct comm) ...":

rwijk@ORA11GR1> select count(comm) from (select distinct comm from emp);

COUNT(COMM)
-----------
4

1 rij is geselecteerd.

Monday, January 19, 2009

PL/SQL session

Today I gave a session about PL/SQL to colleagues who mostly work with Tibco in our Enterprise Application Integration (EAI) group. They know how to program and they know SQL. The challenge was to cover most of the main features of PL/SQL in a workshop in only 8 hours. The session contained lots of little exercises. Topics were architecture, datatypes, main statements, exception handling, appearances of PL/SQL, SQL in PL/SQL, cursors, collections and recordtypes, bulk processing and dynamic SQL.

For those who are interested and who can read Dutch: you can download the document, presentation and exercises here:

PL/SQL-sessie

Bijbehorende powerpoint voor hoofdstuk 1

Alle scripts