Oracle snippets, patterns and other stuff.

_oracle-snippets.txt

Oracle snippets, patterns and other stuff.

coalesce_vs_nvl.sql

declare
  bar varchar2(3) := 'bar';
 
  function foo return varchar2
  is
  begin
    dbms_output.put_line('executing foo');
    return 'foo';
  end;
 
begin
  dbms_output.put_line('coalesce():');
  dbms_output.put_line(coalesce(bar, foo)); -- Can be more than two arguments.
  dbms_output.put_line('');
  dbms_output.put_line('nvl():');
  dbms_output.put_line(nvl(bar, foo)); -- Only two argiments.
end;

get-substrings-using-delimiter.sql

select regexp_substr(t.str, '[^;]+', 1, level) as val
from
  (select 'val1;val2;val3' as str from dual) t
connect by instr(t.str, ';', 1, level - 1) > 0
;

listagg.sql

create table demotable(group_id number, name varchar2(100));

insert into demotable values(1, 'David');
insert into demotable values(1, 'John');
insert into demotable values(1, 'Alan');
insert into demotable values(1, 'David');
insert into demotable values(2, 'Julie');
insert into demotable values(2, 'Charles');
commit;

select
  group_id,
  listagg(name, ', ') within group (order by name) as names
from
  (
    select distinct
      group_id,
      name
    from demotable
  )
group by group_id;

/* output:
group_id  names
       1  Alan, David, John
       2  Charles, Julie
*/

-- another simple example
select
  listagg(column_value, ', ')
    within group(order by column_value desc) as lst
from table(list('a', 'b', 'c'));

/* output:
lst
c, b, a
*/

/* Further reading
   1. http://www.oracle-developer.net/display.php?id=515
*/

multi-raname.sql

begin
  for r in (
    select
      table_name as old_table_name,
      replace(table_name, 'foo', 'bar') as new_table_name
    from user_tables
    where regexp_like(table_name, 'certain expression')
  )
  loop
    execute immediate
      'alter table schema.' || r.old_table_name || ' rename to ' || r.new_table_name;
  end loop;
end;

tablespaces-size.sql

select
  b.tablespace_name,
  b.tbs_size as total_gb,
  a.free_space as free_gb
from
  (
    select
      tablespace_name,
      round(sum(bytes) / 1024 / 1024 / 1024, 2) as free_space
    from dba_free_space
    group by tablespace_name
  ) a,
  (
    select
      tablespace_name,
      round(sum(bytes) / 1024 / 1024 / 1024, 2) as tbs_size
    from dba_data_files
    group by tablespace_name
  ) b
where a.tablespace_name(+) = b.tablespace_name;