Question:
When I called a stored procedure from a trigger, I got a seemingly wrong error message
'invalid request BLR at offset yyy, parameter mismatch for procedure XXX'
but the passed parameters were fine.
Answer:
The solution is to handle the return value.
A stored procedure that returns one or more values per row in the result set cannot be called with execute procedure ... Instead you have to select those returned values into some variables (of the correct type, of course.. but a wrong type would cause a compilation error).
Also remember if there is a chance to have more than one row returned, then you need to put a FOR statement in front of it, as the 3rd example shows. If you suspect that there could be several rows returned but you only care for the first one, then
make sure that the results come in a defined order
or
modify your query and use the MIN() operator to make sure that only one row comes back.
See the sample code below..
// this one does not work:
// execute procedure update_petrochemical_feedstocks (1800024, 2001);
// this one does work:
declare variable v_sd integer;
declare variable v_fp integer;
declare variable v_ar integer;
begin
select * from update_petrochemical_feedstocks (1800024, 2001)
into :v_sd, :v_fp, :v_ar;
end
// multiple rows? then you neeed to use FOR
begin
for
select * from update_petrochemical_feedstocks (1800024, 2001)
into :v_sd, :v_fp, :v_ar
do;
end
// force it to be only one row with the MIN() operator
begin
select MIN(sd_id)
from update_petrochemical_feedstocks (1800024, 2001)
into :v_sd;
end