Can you solve this?

I did the worst thing to myself just before the start of holidays..thats by reading some sql question somewhere on the list and jumping in to solve it. (that was complex enough that it was actually reserved to be asked to the Oracle guru Tom Kyte)

Obviously over the holidays I didnt get too much time..but with whatever little I could work, I havent been able to accomplish much.

Just posting the question for those like-minded souls who get a kick out of solving everything in SQL.

SQL> col id    format 99
SQL> col customer_id  format 99  heading CID
SQL> col service_name  format a12 trunc
SQL> col start_date  format a12
SQL> col end_date  format a12

SQL> desc sql_test
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ID                                        NOT NULL NUMBER(38)
 CUSTOMER_ID                                        NUMBER(10)
 SERVICE_NAME                                       VARCHAR2(100)
 START_DATE                                         DATE
 END_DATE                                           DATE

SQL> select * from sql_test ;

 ID CID SERVICE_NAME START_DATE   END_DATE
--- --- ------------ ------------ ------------
  1   1 Service-S2   01-AUG-07    05-AUG-07
  2   1 Service-S1   02-AUG-07    01-NOV-07
  3   1 Service-S3   07-AUG-07    30-AUG-07
  4   1 Service-S4   07-AUG-07    30-NOV-07
  5   1 Service-S5   02-DEC-07    20-DEC-07

assume thats a table with customer info..what we need to output is the period during which customer had continued service..(doesnt matter what the service is..but as long as its continous..we need to consider it one row)

so the output should be something like this

CID  START_DATE   END_DATE
---  ------------ ------------
  1  01-AUG-07    30-NOV-07 
  1  02-DEC-07    20-DEC-07

PS: extend your solution to work for many other customers too (the sample is only for one customer_id (identified as 1)

Even though I havent solved this yet (well I did, but only to find if I change the data..it fails :) )

(the only good thing so far is..I never worked with MODEL clause before..now I have extensively researched that to solve this..thats a hint hint)

"I am a Senior DBA..but I am NEW"

I don't know if this is real or meant as joke..but had a good laugh when I read that in Oracle forums.

Here is the thread URL

and here is the scoop

"
Hi everyone,

Can any one advise me? I have joined a company as new senior dba.

i am not understanding what shall be done at beginning?

Can anybody advice me how to check all the database and what to do in the beginning ?

I have been reading all the stuff,documents fr. a week?

"

:) I am looked at my business card and it says I am a "Senior oracle DBA" too. Something I should rethink about :)..

Happy Holidays!!

lobs vs strings

Recently one of the emails I read at a client site (wherein the lead had recommended his team to use dbms_lob.substr in the sqls) probed me do this research and as always learning new stuff in oracle is never ending. :)

SqL>Create table test_clob (c1 clob,c2 number); Table created. SqL>ed Wrote file afiedt.buf 1* Insert into test_clob values (Rpad('Test',50000,'*'),null) SqL>/ 1 row created. SqL>ed Wrote file afiedt.buf 1* Insert into test_clob values (Rpad('Test2',100000,'*'),null) SqL>/ 1 row created. SqL>update test_clob set c2=dbms_lob.getlength(c1); 2 rows updated. SqL>commit; Commit complete. SqL>set long 30 SqL>col c1 format a30 trunc SqL>select c1,c2 from test_clob; C1 C2 ------------------------------ ---------- Test************************** 4000 Test2************************* 4000 SqL>ed Wrote file afiedt.buf 1* select c1,c2,length(c1) from test_clob SqL>/ C1 C2 LENGTH(C1) ------------------------------ ---------- ---------- Test************************** 4000 4000 Test2************************* 4000 4000 SqL>ed Wrote file afiedt.buf 1 declare 2 lv_tmp clob; 3 lv_tmp1 varchar2(4000); 4 begin 5 lv_tmp1:= Rpad('Test3',4000,'*'); 6 for i in 1..10 loop 7 lv_tmp:= lv_tmp||lv_tmp1; 8 end loop; 9 Insert into test_clob(c1) values (lv_tmp); 10 commit; 11* end; 12 / PL/SQL procedure successfully completed. SqL>update test_clob set c2=dbms_lob.getlength(c1) where c2 is null; 1 row updated. SqL>commit; Commit complete. SqL>select c1,c2,length(c1) from test_clob; C1 C2 LENGTH(C1) ------------------------------ ---------- ---------- Test************************** 4000 4000 Test2************************* 4000 4000 Test3************************* 40000 40000

first interesting thing I noticed was I couldnt use rpad function to generate a string more than 4000 chrs..Its strange that oracle silently ignores the request to create longer strings (request was made to make it as long as 100000 chrs long) but it extended only upto 4000.

Well, that takes me to using PLSQL to insert clob data.

now comes the interesting part..lets say we need to fetch only part of the data from clob..what would you use? dbms_lob.substr function or regular substr function?

SqL>ed Wrote file afiedt.buf 1 With v1 as (select &length len,&starting_chr startpos from dual) 2 select 3 length(substr(c1,startpos,len)) substr_len 4 , length(dbms_lob.substr(c1,len,startpos)) substr_lob 5* from test_clob,v1 where c2=40000 SqL>/ Enter value for length: 4000 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 4000 len,1 startpos from dual) SUBSTR_LEN SUBSTR_LOB ---------- ---------- 4000 4000 SqL>/ Enter value for length: 4001 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 4001 len,1 startpos from dual) , length(dbms_lob.substr(c1,len,startpos)) substr_lob * ERROR at line 4: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 SqL>ed Wrote file afiedt.buf 1 With v1 as (select &length len,&starting_chr startpos from dual) 2 select 3 length(substr(c1,startpos,len)) substr_len 4 , dbms_lob.getlength(substr(c1,startpos,len)) substr_lob 5 -- , length(dbms_lob.substr(c1,len,startpos)) substr_lob 6* from test_clob,v1 where c2=40000 SqL>/ Enter value for length: 5000 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 5000 len,1 startpos from dual) SUBSTR_LEN SUBSTR_LOB ---------- ---------- 5000 5000

what does that tell? dbms_lob.substr cannot return more than 4K characters ..whereas substr can..

It all comes back to datatypes and why understanding them is necessary..I checked the manual and its clearly stated substr can take a clob as its input and return "clob" ..whereas dbms_lob if it accepts clob, returns varchar2 (hence limited to 4000 characters).

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