Within the plsql function acs_message__new line 39 there is a chunk of code that calls another plsql function apm__get_value
select apm__get_value(package_id, 'SystemURL') into v_system_url from apm_packages where package_key='acs-kernel';
The plsql function apm__get_value has a bug in the query:
select attr_value into value from apm_parameter_values v
where v.package_id = get_value__package_id
and parameter_id = get_value__parameter_id;
There issue is that get_value__parameter_id does not exist.
Please correct me if i am wrong, but the properly value in the where clause must be v_parameter_id instead of get_value__parameter_id
since the variable is declared in the implementation scope.
create or replace function apm__get_value(int4,varchar) returns varchar as '
1:declare
2: get_value__package_id alias for $1;
3: get_value__parameter_name alias for $2;
4: v_parameter_id apm_parameter_values.parameter_id%TYPE;
5: value apm_parameter_values.attr_value%TYPE;
6:begin
7: v_parameter_id := apm__id_for_name (get_value__package_id, get_value__parameter_name);
8:
9: select attr_value into value from apm_parameter_values v
10: where v.package_id = get_value__package_id
11: and parameter_id = get_value__parameter_id;
12:
13: return value;
14:
15:end;' language 'plpgsql';
Unless you guys want to make the parameter_id as a new argument in the plsql function signature, which would stay as:
create or replace function apm__get_value(int4,varchar,int4) returns varchar as '
1:declare
2: get_value__package_id alias for $1;
3: get_value__parameter_name alias for $2;
3: get_value__parameter_id alias for $3;
...
cheers,
