SQL


SQL query for queue table with priorities 6

Queue tables are used to take care of events. There is a mechanism that insert rows and another that takes care of the existing rows, usually deleting them at the end.

When possible one should use Oracle Advanced Queuing mechanism which takes care of managing the queue and a simple request will give you the next in the line.

Some applications develop their own queuing systems and it is a good way to learn how queue works.

Imagine a table with a list of elements and two specific columns:

  • order of arrival
  • priority

The first to be served is the one that, having the highest priority, was the first to arrive.

(more…)

Average size of set of rows from a table

Sometimes users have strange requests that I avoid asking the reason.

Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table.

This reminded me from university times, when doing SQL exercises.

With Oracle 11g and listagg function, it is quite easy to fulfill the request:

set serveroutput on;

declare
  col_list varchar(2500);
  result number;
begin
select 'select round('||listagg('nvl(avg(length('||column_name||')),0)','+') within group (order by column_id)||') 
  from MYAPP.mytable
  where creationdate > to_date(''01.01.2018'',''dd.mm.yyyy'') 
    and creationdate < to_date(''31.12.2018'',''dd.mm.yyyy'')' into col_list 
from all_tab_columns where owner='MYAPP' and table_name='MYTABLE' ;
execute immediate (col_list) into result;
dbms_output.put_line(result);
end;
/