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.

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.

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.