Splitting a String into Elements

Every 4 months or so I need a simple way to split a string (VARCHAR2) into elements, where the elements are separated with some fixed value (a comma, a colon, or perhaps a longer string). Since my short-term memory is too short⁰, I figured I should make a reminder here. Of course, you'll find this on Stackoverflow as well.

There is this function in APEX, which is usually¹ available for you in the database, even if you are not using APEX. Here is a short demo:

declare
  l_elements_arr apex_application_global.vc_arr2;
  l_str varchar2(2000) := 'IPA***Stout***Porter***Pale Ale';
  l_sep varchar2(10) := '***';
begin
  l_elements_arr := apex_util.string_to_table(l_str, l_sep);
  for i in 1..l_elements_arr.count loop
    dbms_output.put_line(l_elements_arr(i));
  end loop;
  l_str := apex_util.table_to_string(l_elements_arr,'|');
  dbms_output.put_line(l_str);
end;
/

The variables for the string and the separator can of course be part of a procedure / function. Also not that the separator can have more than one character. I used three stars because beer is probably good for you.

Another demo inspired by this Oracle blog post:

declare 
  l_str varchar2(100) := 'I$$bought$$a$$3D printer$$this weekend';
  l_sep varchar2(10) := '$$';
  l_exp varchar2(200) := '[^' || l_sep || ']+' ;
begin
  for e in (select regexp_substr(l_str,l_exp, 1, level) str
    from dual
    connect by regexp_substr(l_str,l_exp, 1, level) is not null) loop
    dbms_output.put_line(e.str);
  end loop;
end;
/

Since the work is done by the SQL statement in the FOR-LOOP, you can easily use this in pure SQL as well.

0) Let's just say that my buffer cache has surrendered space to the library cache, because there is so much interesting stuff going on, and all that parsing takes space. 
1) "Usually", because APEX is installed when creating the database, unless you deselect one of the standard components, which would, in almost all cases (that is, more often than what "usually" means), would be a mistake. If you regret your mistake, just download the latest version and install it. That will take less time than it takes to teach me write proper sentences with an adequate use of strange words, and commas.

Delete Cascade with Recursive PL/SQL

If you need to delete all rows in a table that has parent keys for other tables' foreign keys, and the foreign keys constraints have not been defined with "on delete cascade", you can do a recursive delete with the following simple procedure.

This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it:

create or replace procedure delete_cascade(
  p_table_name in user_tables.table_name%type) is
  l_pk user_constraints.constraint_name%type;
begin
  select constraint_name into l_pk
  from user_constraints
  where Constraint_Type='P'
  and table_name=upper(p_table_name);
  for c in (
  select table_name
  from user_constraints
  where R_CONSTRAINT_NAME=l_pk) loop
    delete_cascade(c.table_name);
  end loop;
  execute immediate 'delete from ' || upper(p_table_name);
exception when no_data_found then -- Tables without constraint
  execute immediate 'delete from ' || upper(p_table_name);;
end;
/

DBMS_INDEX_UTL

Here the other day I came across this package in a PL/SQL procedure written by someone else. From the name I reckoned it was a standard package from Oracle, but I had never seen it before. It is not mentioned in the manual Database PL/SQL Packages and Types Reference, and I could not find much about it at My Oracle Support either. Anyway, with SQL Developer you'll get what you need by hitting Shift-F4 (with the cursor at the name of the package). The API is pretty good documented in the comments. The package is used to rebuild indexes, either for a named table, a named schema, or a list of indexes plus some stuff I didn't bother to look into.

Now, there may be a reason why this is not well documented and better known.

Collatz conjecture in PL/SQL

A simple implementation of Collatz conjecture in PL/SQL:

create or replace type int_tab_typ is table of integer;
/

create or replace function collatz(p_n in integer) 
   RETURN int_tab_typ PIPELINED as
   n integer;
BEGIN
  if p_n < 1 or mod(p_n,1)>0 then 
    RETURN ;
  end if;
  n:=p_n;
  while n > 1 
  loop
    pipe row (n);
    if mod(n,2)=1 then 
      n:=3*n+1;
    else 
      n:=n / 2;
    end if;
  end loop;
  pipe row(n);
end;
/

select * from table(collatz(101));

More on Collatz Conjecture (Wikipedia).

The point? Just for fun. Showed it to my 11 years old kid and asked him to figure out the 17 steps sequence of collatz(7) in his head. "Is this going to stop?", he asked half through. "That is what we are going to find out, nobody really knows", I told him.

If you have absolutely nothing useful to do, but have CPU and storage to waste, you may create a table containing n and the number of steps for collatz(n), with values of n ranging from 1 to 2³¹-1 or until storage is exhausted, whichever comes first. Before doing so you may consider changing the function above to just count the steps and look up previous calculations from the table.