You may know that some applications generate queries with bind variables’ names like :1 or :”1″, and neither SQL*Plus nor SQLCl support such variables:
SQLPlus:
SQL> var 1 number;
SP2-0553: Illegal variable name "1".
SQLCL:
SQL> var 1 number;
ILLEGAL Variable Name "1"
So we can’t run such queries as-is, but, obviously, we can wrap them into anonymous PL/SQL blocks and even create a special script for that:
Continue reading→Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.
[sourcecode language=”sql”]
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
[/sourcecode]
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;
Test data:
create table t_str as
select round(dbms_random.value(1e10,9e10)) str from dual connect by level<=1e5
/
PL/SQL variant:
with
function ff(s varchar2) return varchar2
as
type avarchars is table of varchar2(100) index by varchar2(1);
st avarchars;
idx varchar2(1);
res varchar2(10);
function iterate( idx in out nocopy varchar2, arr in out nocopy avarchars)
return boolean
as --pragma inline;
begin
if idx is null
then idx:=arr.first;
else idx:=arr.next(idx);
end if;
return idx is not null;
end;
begin
for i in 1..length(s) loop
st(substr(s,i,1)):=1;
end loop;
while iterate(idx,st) loop
res:=res||idx;
end loop;
return res;
end;
select min(ff(str)) res
from t_str
/
SQL-only variant:
select min(fstr)
from t_str t
cross apply (
select listagg(c) within group (order by 1) fstr
from (select
distinct substr(t.str, level, 1) c
from dual
connect by level <= length(t.str)
)
)
/
Timings:
SQL> create table t_str as
2 select round(dbms_random.value(1e10,9e10)) str from dual connect by level<=1e5
3 /
Table created.
Elapsed: 00:00:00.55
SQL> with
2 function ff(s varchar2) return varchar2
3 as
4 type avarchars is table of varchar2(100) index by varchar2(1);
5 st avarchars;
6 idx varchar2(1);
7 res varchar2(10);
8
9 function iterate( idx in out nocopy varchar2, arr in out nocopy avarchars)
10 return boolean
11 as --pragma inline;
12 begin
13 if idx is null
14 then idx:=arr.first;
15 else idx:=arr.next(idx);
16 end if;
17 return idx is not null;
18 end;
19 begin
20 for i in 1..length(s) loop
21 st(substr(s,i,1)):=1;
22 end loop;
23 while iterate(idx,st) loop
24 res:=res||idx;
25 end loop;
26 return res;
27 end;
28
29 select min(ff(str)) res
30 from t_str
31 /
RES
--------------------------------------------------------------
0123
Elapsed: 00:00:00.48
SQL> select min(fstr) res2
2 from t_str t
3 cross apply (
4 select listagg(c) within group (order by 1) fstr
5 from (select
6 distinct substr(t.str, level, 1) c
7 from dual
8 connect by level <= length(t.str)
9 )
10 )
11 /
RES2
--------------------------------------------------------------
0123
Elapsed: 00:00:01.01
And much easier variant if you need your strings contain digits only:
select min(translate('0123456789', translate('z0123456789','z'||str,'z'), chr(0)))
from t_str
I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:
with a as (select level a from dual connect by level<10)
,b as (select 0 b from dual)
,c as (select 0 c from dual)
select
*
from a,
lateral(select * from b where a.a=b.b)(+) bb
--left outer join c on c.c=bb.b
where a=1;
A B
---------- ----------
1
But doesn’t if we uncomment “left join”:
with a as (select level a from dual connect by level<10)
,b as (select 0 b from dual)
,c as (select 0 c from dual)
select
*
from a,
lateral(select * from b where a.a=b.b)(+) bb
left outer join c on c.c=bb.b
where a=1;
no rows selected
And outer apply works fine:
with a as (select level a from dual connect by level<10)
,b as (select 0 b from dual)
,c as (select 0 c from dual)
select
*
from a
outer apply (select * from b where a.a=b.b) bb
left outer join c on c.c=bb.b
where a=1;
A B C
---------- ---------- ----------
1
Some time ago there was a question on the sql.ru about splitting validity periods for gym cards to enabled and blocked periods:
-- we have a table with gym cards (card id, validity period: from/to)
CREATE TABLE cards(id_card NUMBER, num_card VARCHAR2(100), dt_from DATE, dt_to DATE);
-- periods when cards were blocked (record id, card id, dates from/to)
CREATE TABLE blocks(id_block NUMBER, id_card NUMBER, dt_from DATE, dt_to DATE);
-- cards data:
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
-- blocks data:
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY'));
COMMIT;
Just for fun, I’ve solved it using match_recognise:
with
cards (id_card, num_card, dt_from, dt_to) as (
select 1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY') from dual union all
select 2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual union all
select 3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual
),blocks (id_block, id_card, dt_from, dt_to) as (
select 101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY') from dual union all
select 102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY') from dual union all
select 103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY') from dual union all
select 201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY') from dual
)
,data as (
select *
from (
select *
from (
select id_card,dt_from,dt_to,'c' t from cards
union all
select id_card,dt_to,dt_from,'b' from blocks
) v
--where v.id_card=1 -- here we can filter card ID if needed
) piv
unpivot(dt for x in (dt_from as 'dt_from', dt_to as 'dt_to'))
)
select *
from data
match_recognize (
partition by id_card
order by dt /* we can modify add "t" into "order by clause" to make it more deterministic */
measures
match_number() as N,
first(dt) as dt_from,
last(dt) as dt_to
pattern (x_start+ x_end)
define
x_start as x = 'dt_from'
,x_end as x = 'dt_to'
);