Pl/Sql

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 short0, 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 usually1 available for you in the database, even if you are not using APEX. Here is a short demo: ...

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: ...

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. ...

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). ...