Nominated for Oracle Database Developer Choice Awards (SQL) – San Francisco calling

I don’t know why and by whom, but I was nominated for the Oracle Database Developer Choice Awards – SQL Category as one of eight SQL developers.


Cite from


The Oracle Database Developer Choice Awards celebrate and recognize technical expertise and contributions in the Oracle Database community. As longtime and new users of Oracle Database move to the Cloud and take advantage of this exciting new architecture, community experts will (more...)

The Fundamental theorem of arithmetic – SQL version

Every positive integer (except the number 1) can be represented in exactly one way apart from rearrangement as a product of one or more primes, see for example Wolfram MathWorld or Wikipedia.

Here is the SQL-Version, we compute this for all integers up to 100

with bound as
select 100 as bound from dual
n_until_bound as (
select level+1 n
from dual
connect by level <= (select bound.bound from bound)
primes_under_bound  (more...)

A Greedy Algorithm using Recursive subquery factoring

Today is friday and I like the twitter-hashtag #FibonacciFriday,
so I tweeted


Don’t be afraid of having a look at the wikipedia-site, the math is not complicated at all ( you don’t need more than adding natural numbers smaller than hundred ), nevertheless the theorem is nice from a (more...)

Overview of all time changes this year via SQL

inspired by Laurent Schneider’s remark concerning special time changes in Lord Howe Island

with dates as
trunc(sysdate, 'year') + level - 1 as day
connect by
extract(year from trunc(sysdate, 'year') + level - 1) = extract(year from trunc(sysdate, 'year'))

timezones as
vtn.TZNAME, listagg(vtn.TZABBREV, ', ') within group(order by vtn.tzabbrev) tzabbrevs
v$timezone_names vtn
group by

offsets as
v.*, d. (more...)

Why is it not possible to raise an exception when handling no_data_needed ?

It seems impossible to raise an exception when handling no_data_needed.


create or replace function demo return sys.odciNumberList pipelined
  pipe row(1);
  pipe row(1/0);
exception when others then
  dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
  raise program_error;

sokrates@11.2 > select * from table(demo());
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero

no rows selected

in exception handler, sqlcode: -1476

As (more...)

PL/SQL oddity

sokrates@12.1 > create procedure p is begin null; end this_does_not_compile;
  2  /

Warning: Procedure created with compilation errors.

sokrates@12.1 > show errors
Errors for PROCEDURE P:

-------- -----------------------------------------------------------------
1/32	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P'
	 at line 1, column 11

sokrates@12.1 > REM expexted
sokrates@12.1 > create function f return number is begin return null; end this_does_not_compile;
  2  /

Warning: Function created with compilation errors.

sokrates@12.1 >  (more...)

Yet Another Elementary SQL Bug


sokrates@12.1 > select banner from v$version;

Oracle Database 12c Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE	Production
TNS for Linux: Version - Production
NLSRTL Version - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

sokrates@12.1 > ! (more...)

How 2 Bytes can fit into 1 Byte

Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.

This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing (more...)

A Restriction of the Cardinality Hint

Here is a restriction of the cardinality hint in conjunction with the materialize-hint ( note: both are undocumented but sometimes of great use ):
we cannot tell the optimizer in the outer query ( the one that uses the materialized subquery ) about the cardinality of the materialization, this can only – and then not always – be done within the materializing query.

The example to show that is stolen from Tom Kyte’s Presentation S13961_Best_Practices_for_Managing_Optimizer_Statistics_Short. (more...)

Issue with updatable views

It’s sometimes amazing, how many bugs there are still with elementary SQL.

Here is one concerning updatable views:

sokrates@12.1 > create table t ( v varchar2(30) );

Table created.

sokrates@12.1 > create view v as
  2  select v as dontdothatman, v as canbelostwheninserted
  3  from t; 

View created.

sokrates@12.1 > insert /* this is fine */ into v 
  2  values('fine', 'fine');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		        (more...)