Forum OpenACS Development: Obscure but nasty bug in apm-procs.tcl

If you have a minute, and have a TEST SERVER available, try the following:

Run
```
ad_parameter -package_id [ad_acs_kernel_id] 
SystemURL
```
in a tcl page or nscp. It should return your site URL.
Navigate to /acs-admin/apm.
Navigate to Services -> acs-kernel.
Click "Manage parameter information".
Add a parameter. DO NOT RESTART YOUR SERVER.
Run ad_parameter again.
(ACS Classic; and probably OpenACS) Watch in horror as it returns "http://yourdomain.com".

This just bit us hard on a production site.

The problem is an incorrect query apm_parameter_cache_update in apm_parameter_register. The query looks like this in ACS Classic:

select v.package_id, p.parameter_name,
       nvl(p.default_value, v.attr_value) as attr_value
from apm_parameters p, apm_parameter_values v
where p.package_key = :package_key
  and p.parameter_id = v.parameter_id (+)

when it should really look like this (Oracle):

select v.package_id, p.parameter_name,
       decode(v.value_id,NULL,p.default_value,v.attr_value) 
attr_value
from apm_parameters p, apm_parameter_values v
where p.package_key = :package_key
  and p.parameter_id = v.parameter_id (+)

Looking at apm-procs.xql (latest CVS, v1.4), I'm pretty sure OpenACS has the same problem.