Wednesday, November 28, 2007

Interesting SQL problem

Came across this interesting sql question on oracle-l list while I was searching for something else.

The poster's original question is as below..


create table t(id number);
insert into t values(1);
insert into t values(2);
commit;

I want to query this with an Id set. All values in the set should be
there to return me any row.
e.g.
select * from t where id in (1,2); return 1 and 2

If am serching for 1,2,3 if any one value is missing I should not get any data.
e.g.
select * from t where id in (1,2,3) should not return any row.
How to rewrite the above query with (1,2,3) that should not return me any row.



Here is my solution for that...


ccD>l
1 With inputstr as
2 ( select '&inp' elements from dual),
3 setdata
4 as
5 (
6 select
7 trim( substr (txt,
8 instr (txt, ',', 1, level ) + 1,
9 instr (txt, ',', 1, level+1)
10 - instr (txt, ',', 1, level) -1 ) )
11 as token
12 from (select ','||elements||',' txt
13 from inputstr ) t,inputstr i
14 connect by level <=
15 length(i.elements)-length(replace(i.elements,',',''))+1
16 )
17 select token from
18 (
19 select
20 to_number(token) token,
21 nvl2(t.id,1,0) present,
22 min(nvl2(t.id,1,0)) over() min_over_report
23 from setdata s, t
24 where s.token=t.id(+)
25 )
26* where min_over_report=1



Testing
--------


ccD>select * from t; -- this is what the table contains
ID
----------
1
2
ccD> get the above sql into buffer
ccD>/
Enter value for inp: 1,2 -- input is 1,2 and it retrives two rows
TOKEN
----------
1
2
ccD>/
Enter value for inp: 1,2,3 -- 1,2,3 retrives no rows because 3 is not present
no rows selected
ccD>/
Enter value for inp: 1,2,3,4 -- same with 1,2,3,4
no rows selected
ccD>/
Enter value for inp: 1 -- input 1 retrives one row
TOKEN
----------
1



Monday, November 26, 2007

Software what?

Just saw this picture online and was thinking to myself..

well this day is not very far away and approaching us very soon. we are going to see "wanted software pros" like our current "open house" signs.

but I am curious how to interpret...
too much demand for software professionals?
or
would that be too much supply in market , that employing headhunters to recruit would be considered "not-worth-the-cost".

Take a look at this picture..




Saturday, November 3, 2007

ASM instance & remote client

well we know ASM instance is always in mount state and so you really dont have much access to data dictionary..and listener displays BLOCKED as status leaving you no choice but to login into the OS server and connect to asm instance locally..

But I just learned something new..that by adding "UR=A" to tnsnames, you can actually connect to asm instance (blocked service) via oracle net connection. (remote connection)

thats really cool thing for me..as I dont have to login to a server box just to check asm instance..I can simply have one more sql window open to asm instance from my client..

Add "(UR=A)" to the connect_data section of your tnsnames entry and you should be all set.

(Note: ASM instance lets admin authentication only via password file or OS authentication..so obviously, you have to have password file created before trying to connect to ASM from remote client)