Wednesday, February 27, 2008

Calendar

Today I saw a query that prints a calendar for an entire year. I modified it slightly to conform with my local (Dutch) settings:

- using ISO-weeknumbers
- a week starts with monday and ends on a sunday

If your country uses different settings, then it's probably easy to adjust the query a little to fit your local settings. You'll also have to translate the column names, but that won't be hard either.

rwijk@ORA11G> column maand format a17
rwijk@ORA11G> column week format a4
rwijk@ORA11G> break on maand skip 1
rwijk@ORA11G> with input as (select sysdate dt from dual)
2 select to_char(mnth,'fmMonth yyyy') "Maand"
3 , monday "Ma"
4 , tuesday "Di"
5 , wednesday "Wo"
6 , thursday "Do"
7 , friday "Vr"
8 , saturday "Za"
9 , sunday "Zo"
10 , lpad(to_char(week,'fmiw'),4) "Week"
11 from ( select day
12 , mod(to_number(to_char(day,'j')),7) daynumber_in_week
13 , trunc(day,'iw') week
14 , trunc(day,'mm') mnth
15 , to_char(day,'iyyy') year
16 from ( select trunc(dt,'y') - 1 + level day
17 from input
18 connect by level <= add_months(trunc(dt,'y'),12) - trunc(dt,'y')
19 )
20 )
21 pivot
22 ( max(lpad(to_char(day,'fmdd'),2))
23 for daynumber_in_week in
24 ( 0 monday, 1 tuesday, 2 wednesday, 3 thursday
25 , 4 friday, 5 saturday, 6 sunday
26 )
27 )
28 order by mnth
29 , year
30 , week
31 /

Maand Ma Di Wo Do Vr Za Zo Week
----------------- -- -- -- -- -- -- -- ----
Januari 2008 1 2 3 4 5 6 1
7 8 9 10 11 12 13 2
14 15 16 17 18 19 20 3
21 22 23 24 25 26 27 4
28 29 30 31 5

Februari 2008 1 2 3 5
4 5 6 7 8 9 10 6
11 12 13 14 15 16 17 7
18 19 20 21 22 23 24 8
25 26 27 28 29 9

Maart 2008 1 2 9
3 4 5 6 7 8 9 10
10 11 12 13 14 15 16 11
17 18 19 20 21 22 23 12
24 25 26 27 28 29 30 13
31 14

April 2008 1 2 3 4 5 6 14
7 8 9 10 11 12 13 15
14 15 16 17 18 19 20 16
21 22 23 24 25 26 27 17
28 29 30 18

Mei 2008 1 2 3 4 18
5 6 7 8 9 10 11 19
12 13 14 15 16 17 18 20
19 20 21 22 23 24 25 21
26 27 28 29 30 31 22

Juni 2008 1 22
2 3 4 5 6 7 8 23
9 10 11 12 13 14 15 24
16 17 18 19 20 21 22 25
23 24 25 26 27 28 29 26
30 27

Juli 2008 1 2 3 4 5 6 27
7 8 9 10 11 12 13 28
14 15 16 17 18 19 20 29
21 22 23 24 25 26 27 30
28 29 30 31 31

Augustus 2008 1 2 3 31
4 5 6 7 8 9 10 32
11 12 13 14 15 16 17 33
18 19 20 21 22 23 24 34
25 26 27 28 29 30 31 35

September 2008 1 2 3 4 5 6 7 36
8 9 10 11 12 13 14 37
15 16 17 18 19 20 21 38
22 23 24 25 26 27 28 39
29 30 40

Oktober 2008 1 2 3 4 5 40
6 7 8 9 10 11 12 41
13 14 15 16 17 18 19 42
20 21 22 23 24 25 26 43
27 28 29 30 31 44

November 2008 1 2 44
3 4 5 6 7 8 9 45
10 11 12 13 14 15 16 46
17 18 19 20 21 22 23 47
24 25 26 27 28 29 30 48

December 2008 1 2 3 4 5 6 7 49
8 9 10 11 12 13 14 50
15 16 17 18 19 20 21 51
22 23 24 25 26 27 28 52
29 30 31 1


62 rijen zijn geselecteerd.


And if you are not on 11g yet, you cannot use the pivot operator, and you'll have to settle with this one:

rwijk@ORA11G> with input as (select sysdate dt from dual)
2 select to_char(mnth,'fmMonth yyyy') "Maand"
3 , max(decode(daynumber_in_week,0,lpad(to_char(day,'fmdd'),2))) "Ma"
4 , max(decode(daynumber_in_week,1,lpad(to_char(day,'fmdd'),2))) "Di"
5 , max(decode(daynumber_in_week,2,lpad(to_char(day,'fmdd'),2))) "Wo"
6 , max(decode(daynumber_in_week,3,lpad(to_char(day,'fmdd'),2))) "Do"
7 , max(decode(daynumber_in_week,4,lpad(to_char(day,'fmdd'),2))) "Vr"
8 , max(decode(daynumber_in_week,5,lpad(to_char(day,'fmdd'),2))) "Za"
9 , max(decode(daynumber_in_week,6,lpad(to_char(day,'fmdd'),2))) "Zo"
10 , lpad(to_char(week,'fmiw'),4) "Week"
11 from ( select day
12 , trunc(day,'iw') week
13 , trunc(day,'mm') mnth
14 , mod(to_number(to_char(day,'j')),7) daynumber_in_week
15 , to_char(day,'iyyy') year
16 from ( select trunc(dt,'y') - 1 + level day
17 from input
18 connect by level <= add_months(trunc(dt,'y'),12) - trunc(dt,'y')
19 )
20 )
21 group by mnth
22 , year
23 , week
24 order by mnth
25 , year
26 , week
27 /

Maand Ma Di Wo Do Vr Za Zo Week
----------------- -- -- -- -- -- -- -- ----
Januari 2008 1 2 3 4 5 6 1
7 8 9 10 11 12 13 2
14 15 16 17 18 19 20 3
21 22 23 24 25 26 27 4
28 29 30 31 5

Februari 2008 1 2 3 5
4 5 6 7 8 9 10 6
11 12 13 14 15 16 17 7
18 19 20 21 22 23 24 8
25 26 27 28 29 9

Maart 2008 1 2 9
3 4 5 6 7 8 9 10
10 11 12 13 14 15 16 11
17 18 19 20 21 22 23 12
24 25 26 27 28 29 30 13
31 14

April 2008 1 2 3 4 5 6 14
7 8 9 10 11 12 13 15
14 15 16 17 18 19 20 16
21 22 23 24 25 26 27 17
28 29 30 18

Mei 2008 1 2 3 4 18
5 6 7 8 9 10 11 19
12 13 14 15 16 17 18 20
19 20 21 22 23 24 25 21
26 27 28 29 30 31 22

Juni 2008 1 22
2 3 4 5 6 7 8 23
9 10 11 12 13 14 15 24
16 17 18 19 20 21 22 25
23 24 25 26 27 28 29 26
30 27

Juli 2008 1 2 3 4 5 6 27
7 8 9 10 11 12 13 28
14 15 16 17 18 19 20 29
21 22 23 24 25 26 27 30
28 29 30 31 31

Augustus 2008 1 2 3 31
4 5 6 7 8 9 10 32
11 12 13 14 15 16 17 33
18 19 20 21 22 23 24 34
25 26 27 28 29 30 31 35

September 2008 1 2 3 4 5 6 7 36
8 9 10 11 12 13 14 37
15 16 17 18 19 20 21 38
22 23 24 25 26 27 28 39
29 30 40

Oktober 2008 1 2 3 4 5 40
6 7 8 9 10 11 12 41
13 14 15 16 17 18 19 42
20 21 22 23 24 25 26 43
27 28 29 30 31 44

November 2008 1 2 44
3 4 5 6 7 8 9 45
10 11 12 13 14 15 16 46
17 18 19 20 21 22 23 47
24 25 26 27 28 29 30 48

December 2008 1 2 3 4 5 6 7 49
8 9 10 11 12 13 14 50
15 16 17 18 19 20 21 51
22 23 24 25 26 27 28 52
29 30 31 1


62 rijen zijn geselecteerd.

Wednesday, February 20, 2008

Invalid synonyms

This morning I read an email discussion about having invalid synonyms in our new Oracle 10.2.0.3 database. I frowned a little, thinking it was probably a project manager mixing up words and concepts picked up by his team members. After all, a synonym cannot be invalid: it is only another word for an object. It's the underlying object that can be invalid or missing. And apparently that's what they meant by "invalid synonyms".

But then a developer colleague who was testing our new environment, wanted me to have a look and showed me invalid synonyms in his SQL Navigator gui. The problem with those tools - I think - is that I don't get to know what query they are executing. So I had to reproduce it myself using SQL*Plus. In our old 9.2.0.7 database:

SQL> create procedure p1
2 is
3 begin
4 null;
5 end p1;
6 /

Procedure is aangemaakt.

SQL> create procedure p2
2 is
3 begin
4 p1;
5 end p2;
6 /

Procedure is aangemaakt.

SQL> create synonym p2_syn for p2
2 /

Synoniem is aangemaakt.

SQL> create synonym p1_syn for p1
2 /

Synoniem is aangemaakt.

SQL> select object_name,status from user_objects
2 where object_name in ('P1','P2','P1_SYN','P2_SYN')
3 /

OBJECT_NAME STATUS
------------------------------ -------
P1 VALID
P1_SYN VALID
P2 VALID
P2_SYN VALID

4 rijen zijn geselecteerd.

SQL> create or replace procedure p1 (p_tekst in varchar2)
2 is
3 begin
4 dbms_output.put_line(p_tekst);
5 end p1;
6 /

Procedure is aangemaakt.

SQL> select object_name,status from user_objects
2 where object_name in ('P1','P2','P1_SYN','P2_SYN')
3 /

OBJECT_NAME STATUS
------------------------------ -------
P1 VALID
P1_SYN VALID
P2 INVALID
P2_SYN VALID

4 rijen zijn geselecteerd.


This was what I expected. The synonym always remains valid as it really doesn't have a status.

This is the same test in our new 10.2.0.3 database:

SQL> create procedure p1
2 is
3 begin
4 null;
5 end p1;
6 /

Procedure is aangemaakt.

SQL> create procedure p2
2 is
3 begin
4 p1;
5 end p2;
6 /

Procedure is aangemaakt.

SQL> create synonym p2_syn for p2
2 /

Synoniem is aangemaakt.

SQL> create synonym p1_syn for p1
2 /

Synoniem is aangemaakt.

SQL> select object_name,status from user_objects
2 where object_name in ('P1','P2','P1_SYN','P2_SYN')
3 /

OBJECT_NAME STATUS
------------------------------ -------
P1 VALID
P1_SYN VALID
P2 VALID
P2_SYN VALID

4 rijen zijn geselecteerd.

SQL> create or replace procedure p1 (p_tekst in varchar2)
2 is
3 begin
4 dbms_output.put_line(p_tekst);
5 end p1;
6 /

Procedure is aangemaakt.

SQL> select object_name,status from user_objects
2 where object_name in ('P1','P2','P1_SYN','P2_SYN')
3 /

OBJECT_NAME STATUS
------------------------------ -------
P1 VALID
P1_SYN INVALID
P2 INVALID
P2_SYN INVALID

4 rijen zijn geselecteerd.


Apparently, Oracle10 marks the synonyms as invalid. And although the definition of the user_objects view changed a bit to reflect new object types, the part that displays the status column did not change. It's still:

decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),


where o is the alias from sys.obj$. So it is the dependency mechanism in Oracle10 that marks the synonyms as invalid when the underlying object changes or becomes invalid. Note that even P1_SYN became invalid, even though the referring P1 procedure changed to another valid procedure.

Saturday, February 16, 2008

Obfuscated SQL contest

On Oracle Community in the Database Developers Group a discussion started about having an Obfuscated SQL contest. It was not immediately clear to me what exactly was meant by this, but further answers explained it like this:

I believe the goal can be summarized as "Show off cool language features, bugs or advanced usage while being as unreadable as possible" .
Drawings are very popular, but are strictly optional.


or:

* To write the most Obscure/Obfuscated SQL query.
* To show the importance of SQL programming style, in an ironic way.
* To stress SQL techniques with unusual code.
* To illustrate some of the subtleties of the SQL language.
* To provide a safe forum for poor SQL code.


It looked like fun to me (maybe because I am responsible for standards and guidelines in my daily work and I want to break free?), so I wrote something which contains a lot of different techniques and is really obscure. It's the first contestant I think and I really hope I will see more queries from the usual suspects.

Of course it can be obscured more, but I have to draw the line somewhere. Note that I enhanced the query slightly to reduce the list of hexadecimal codes and to introduce a regexp_replace, an until clause and grouping sets. Ok, here it is:

rwijk@ORA11G>                             select
2 replace(extract(xmlagg(xmlelement(
3 "ILoveSQL",y||',')order by z),'/ILoveSQL/text()'),
4 ',')x from(select row_number()over(partition by b order
5 by null)z,b,case when to_char(b||row_number()over(partition
6 by b order by null),'fmxxx')in('22f','223','222','220','21f'
7 ,'21c','21b','21a','213','211','20b','207','205','204','203',
8 '202','1ff','37' ,'33','1c9','1c5','1c3',
9 '1bf','1bb','1b7' ,'1b3','1b1','1ab',
10 '1a9','1a5','1a1' ,'19f','31','2b',
11 '166','165','164'
12 ,'15b','15a','158'
13 ,'157','14f','14e'
14 ,'14d','14c','149'
15 ,'145','142','141'
16 ,'140','13d','13c'
17 ,'137','22','21',
18 '20','101','fd',
19 'fb','f7','f3',
20 'ef','eb','e9',
21 'e5','e3','dd',
22 'd9','d7','1d',
23 '17','9f','8d',
24 '89','83','7d'
25 ,
26 '7a','79'
27 ,'78','6f','f','b'
28 )then chr(sqrt(power
29 (2,10))) else
30 regexp_substr(a,
31 chr(46)
32 ,
33 row_number
34 ()over(partition by b order by null)
35 ,1,'i')end y from(select concat(s||t||r||a||n||g||e
36 ,w||o||d) a,rownum b from(select s,k,i from ( select s,k,i
37 from user_users where ln(34e5)=exp(ascii('@')-2e2) model dimension
38 by(0 i,-3 k)measures(external_name s)rules iterate(12345)until(ascii
39 (s[iteration_number ,-8])-ascii(s[0,-8])=20)
40 (s[iteration_number ,for k from -10 to -2
41 increment 2]= regexp_replace(lpad(
42 chr(iteration_number
43 +decode( sign(
44 iteration_number-6),-1,
45 65,0,73,76)),decode(mod
46 (cv(k),4),0,3,-2,5),chr
47 (iteration_number+101
48 -decode (sign(
49 iteration_number-6),
50 -1,36,0,28,25))),
51 '(.'||decode(mod
52 (cv(k),4),0,null
53 ,-2,'{5}')||chr
54 (41),'\1 '))))
55 pivot(max(s)for
56 i in (6 s,1 t,5
57 r,9 a,7 n,2 g,0
58 e,8 w,4 o,3 d))
59 )
60 ,table
61 (cast(multiset(select
62 null from dual connect
63 by rownum<=length(a))as
64 sys.dbms_debug_vc2coll)
65 ))group by grouping
66 sets((b))
67 /

X
----------------------------------------------------------------------------

OOO BBBB FFFFF U U SSSS CCCC AAA TTTTT EEEEE DDDD
O O B B F U U S C A A T E D D
O O BBBB FFF U U SSS C AAAAA T EEEEE D D
O O B B F U U S C A A T E D D
OOO BBBB F UUU SSSS CCCC A A T EEEEE DDDD

5 rijen zijn geselecteerd.