2011-02-28

NOCOUG Second SQL Challenge

Working with the second NoCOUg SQL Challenge. In the magazine there can be found also "advice for an Oracle Beginner" articles - worth reading.




Here is my five cents to towards the problem. Another answers may be found from Iggy Fernandez blog comments.

When I found the challenge, there were already some published answers to the riddle. So I started with minimizing the starting set. Got rid of nulls in the first place. And after a while ended up with a hierarchical query. On a way I draw a Graphviz picture of the riddle data. Maybe that visualizes some paths I was trying to follow trying to figure out alternative solutions. SQL commands for creating the required data.




with aa as (
select word1, word2, word3, word2 gr
from riddle
where word1 is not null
), bb as (
select gr,pre,word
from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
, first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
, first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
, min(pre)over(partition by word) mi
, max(pre)over(partition by word) ma
from bb
), dd (gr,mi,ma,pre,word,ord)as (
select gr,mi,ma,pre,word,cast(2 as varchar2(10))
from cc
where cc.pre=2 and cc.bg is null and cc.ag is null
union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
, dd.ord||case when cc.pre = 1 then cc.mi else cc.ma end
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by rpad(dd.ord,10,'2'))
from dd
;



Update 8.3.2011
Ordering with rpad seems like so borrowed from the riddle_tree. So here is another solution that maintains the ordering number while browsing the tree.



with aa as (
select word1, word2, word3, word2 gr
from riddle
where word1 is not null
), bb as (
select gr,pre,word
from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
, first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
, first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
, min(pre)over(partition by word) mi
, max(pre)over(partition by word) ma
from bb
), dd (gr,mi,ma,pre,word,nord,lv)as (
select gr,mi,ma,pre,word,2222222,1
from cc
where cc.pre=2 and cc.bg is null and cc.ag is null
union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
, dd.nord+(cc.pre-2)*power(10,6-dd.lv)
, dd.lv+1
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by dd.nord)
from dd
;


2011-02-23

revoke through a db link

Why would someone do such a thing? Following will end up an active a user session stuck waiting "SQL*Net message from dblink".



sqlplus /nolog

connect / as sysdba


drop user a cascade;

drop user b cascade;


create user a identified by a;

grant create session to a;

grant create procedure to a;

grant create database link to a;


create user b identified by b;

grant create session to b;


connect a/a

declare
comm varchar2(200);
begin
select 'create database link b connect to b identified by b using '''||
sys_context('userenv','db_unique_name')||'''' into comm
from dual;
execute immediate comm;
end;
/


create or replace procedure p as
begin
execute immediate 'revoke execute on p from b';
end;
/


grant execute on p to b;

exec a.p@b

2011-02-09

not exists null

I wrote earlier about not in and null. Be careful also when using not exists predicate together with a sub query resulting nulls.


SQL> select 1 from dual where not exists (select 1 from dual where 1=0);

1
----------
1

SQL> select 1 from dual where 1=0;

no rows selected



SQL> select 1 from dual where not exists (select max(1) from dual where 1=1);

no rows selected

SQL> select max(1) from dual where 1=1;

MAX(1)
----------
1



SQL> select 1 from dual where not exists (select max(1) from dual where 1=0);

no rows selected

SQL> select max(1) from dual where 1=0;

MAX(1)
----------


SQL> select 1 from dual where not exists (select null from dual);

no rows selected

SQL> select null from dual;

N
-



In the sub query there is a row but it is null, unknown. So not exists null evaluates to false. More about the issue in responses to a oracle-l mailing list post.

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.