Oracle Forms

I have a 10G form, which has a dropdown list of values. Depending on the value selected, I look up a setup table which will tell me the name of the database package/function to call.

I then want to call that function with a bind IN parameter and then retrieve the function return value (which is a varchar2 containing message(s) as a stream of text) to display to the user.

I have searched high and low and cannot find a good example.
(a) forms_ddl doesn't seem to have the ability
(b) dbms_sql - some forums/blogs warn away from this due to db version dependencies
(c) exec_sql seems to be very basic and more aimed at odbc calls, rather than calls to native oracle db functions.

Here is example pseudo-code of what I am attempting to do on a WHEN-BUTTON-PRESSED trigger
DECLARE
v_Param1 VARCHAR2 := 'myInputValue';
v_FunctionName VARCHAR2 := 'MYDBPKG.MYFUNCTION';
v_DynamicSQL VARCHAR2;
v_Result VARCHAR2;
BEGIN
v_DynamicSQL := 'BEGIN :v_result:='||v_FunctionName||'('||chr(39)||v_Param1||chr(39)||')'; END';
Bind v_result variable;
execute dynamic sql;
message('the resulting text was <'||v_result||'>');
END;

Obviously, my code above has the function name hard-coded, but my real-life code would retrieve this function name from a database table, so I cannot call the function directly in the Forms PL/Sql.

where the db package/function looks like this:
create or replace package MYDBPKG as
function myfunction(I_Param VARCHAR2) return VARCHAR2;
end mydbpkg;


Anybody got a good example ?

Thanks

Alan

Edited by: Alan Lawlor on 11-May-2011 09:34