connect / as sysbda without password not working? (19c)

If you get "username/password invalid" error while connecting on server machine as oracle user and you think you have done everything correctly ("oracle" is member of groups "dba" and "oper"), check this latest step…More...

Gap with MATCH_RECOGNIZE

Finding gaps in date intervals is a classical and potentially costly operation in the general case where intervals may overlap.
Here is a simple code pattern using
match_recognize More...

Until 19c LISTAGG doesn't support DISTINCT, any workaround?

Several solutions exist: with REGEXP, custom PL/SQL procedure, LAG combined with DECODE…
Let's see + and - of each one…
More...

Rounding and truncating DATE at 5,10,15 and 30 min

Quite common need in business application: round the minute of an hour to specific value of which common ones are 5,10,15 and 30 minutesMore...

Split a string using REGEXP and CONNECT BY


You have a string of tokens separated by a char, here is how to split it using
regexp and connect by to return a table usable in a WHERE IN conditionMore...

Generating table of intervals (1,10),(11,20),...,(101,200),...,(1001,2000),...

It's often useful to present statistical results by interval representing frequencies but not necessarily evenly spaced,
example with (task, person) associations,
how many persons with only 1 task, with 2 to 10, with 11 to 20, … with 101 to 200, …
More...

Merging date intervals with MATCH_RECOGNIZE

Merging date intervals is a classical and potentially costly operation in the general case where intervals may overlap.
Here is a simple code pattern using
match_recognize More...

ORACLE LONG TO VARCHAR2 conversion

yes, ... we know: don't use LONG, don't use LONG... But LONG is used by lot of legacy meta-data tables in ORACLE... so in case you need to use their content as a VARCHAR2 (to concat, subset, ...) then you have no choice to have a way to convert them to VARCHAR2 in a way that can be used in a SELECT.
More...