Saturday, March 27, 2010

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;
if p_n < 1 or mod(p_n,1)>0 then
end if;
while n > 1
pipe row (n);
if mod(n,2)=1 then
n:=n / 2;
end if;
end loop;
pipe row(n);

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

Saturday, March 6, 2010

Using trace to find wallet location

Not talking about a lost wallet with money, but the wallet used for encryption in the Oracle database and with encrypted backup.

A wallet is used for storing keys used to encrypt/decrypt data to/from the database, as with Transparent Data Encryption. The documentation on Advanced Security says that Oracle searches the parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora, or if not found it searches for WALLET_LOCATION in the same file. If none of them are given it searches in the default location of the wallet for the database. I found different references to where this default location is, depending on underlying OS and version. After a few rounds of trial and error (always receiving ORA-28368) I gave up and resorted to tracing my process:

select p.spid
from v$session s join v$process p on(s.paddr=p.addr)
where s.sid in (select sid from v$mystat);

Then using strace on the process:

strace -p 42

Among the output from strace something like this can be found:

access("/u01/app/oracle/admin/FOO/wallet/ewallet.p12", F_OK) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/admin/FOO/wallet/ewallet.p12", O_RDWR|O_CREAT|O_TRUNC, 0666) = -1 ENOENT (No such file or directory)

In this case the directory did not exist. It needs to be created with proper privileges for the OS user running the instance (usually oracle).

On Windows I imagine Process Monitor can be used to track down the location.

Morgans Library has a nice reference on wallets.