Tag Archives: funny

v$blog #funny #friday

Posted on by Posted in curious, Funny, oracle Leave a comment 
select title, short_url 
from v$blog 
where pubDate>=systimestamp - interval '5' month;

with v$blog as (
    select  
        title
       ,to_timestamp_tz(pubDate,'DY, dd mon yyyy hh24:mi:ss TZR') pubDate
       ,short_url
       ,description
    from xmltable(
            '/rss/channel/item'
            passing
                xmltype(httpuritype('https://orasql.org/feed/').getclob())
            columns
                 title       varchar2(70)  path 'title'
                ,pubDate     varchar2(40)   path 'pubDate'
                ,url         varchar2(128)  path 'link'
                ,short_url   varchar2(50)   path 'guid'
                ,description varchar2(500)  path 'fn:substring(./description,1,500)'
    ) 
)
select title, short_url from v$blog where pubDate>=systimestamp - interval '5' month;

Funny friday Oracle SQL quiz: query running N seconds

Posted on by Posted in curious, Funny, oracle, quiz, SQL Leave a comment

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution 

with v(start_hsecs, delta, flag) as (
  select
    hsecs as start_hsecs, 0 as delta, 1 as flag
  from v$timer
  union all
  select
    v.start_hsecs,
    (t.hsecs-v.start_hsecs)/100 as delta,
    case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
  from v, v$timer t
  where v.flag>0 and t.hsecs>=v.start_hsecs
)
select delta
from v
where 0>flag
/
--end

[collapse]
SQL> var N number
SQL> exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL> select...

     DELTA
----------
      3.01

1 row selected.

Elapsed: 00:00:03.01

Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:

select count(*) from dual 
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;