Oracle tips

tipsOracle.md

Oracle Tips

Profiling

• PL/SQL Code coverage

Dates and queries

Number of day of the week

Being 1 for monday to 7 for sunday.

select 1 + trunc(sysdate) - trunc(sysdate, 'IW') from dual;

Last 12 mondays

select trunc(next_day((sysdate - 7 * (12 - rownum + 1)), 'MON'))
from dual
connect by level <= 12
order by 1 desc

Tricks

Select from dual with 10 rows.

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <=10 ORDER BY 1 ASC;

Timestamp expression

Filter a timestamp column without use of TO_TIMESTAMP:

SELECT foo
FROM   bar
WHERE  date_created = TIMESTAMP '2018-02-26 00:00:00.000000'