This content has been marked as final. Show 9 replies
You can use EXECUTE IMMEDIATE to call pl/sql dynamically. But you have to wrap this in a db-procedure.
btw.. Whats the reason for this dynamical approach, as it is very unusual?
DECLARE v_value number; V_FUNC VARCHAR2(100); V_PARAM VARCHAR2(100); begin select FUNCTION_NAME, PARAM INTO V_FUNC, V_PARAM from function_table where id =1; message('V_FUNC='||V_FUNC); pause; execute immediate('select ' || v_func || ' from dual') into v_value; :text01 := v_value; end;
Below is the modified code:
select FUNCTION_NAME, PARAM
INTO V_FUNC, V_PARAM
where id =1;
-- create a valuetable with one column vval varchar2 type
v_cmd := 'declare val varchar2(100); Begin val := '||v_func||'; delete from valuetable; insert into valuetable(vval) values (v); commit; end;';
select vval into :text01 from valuetable where rownum = 1;
when no_data_found then
-- do your action....
Edited by: user5213229 on Feb 2, 2013 4:33 PM
And what happens if two users start that function at the same time?
Simply Create that table global temporary table for session.
create global temporary table valuetable (vval varchar2(100)) on commit preserve rows;
Edited by: user5213229 on Feb 4, 2013 5:31 PM
If you use dynamic SQL in a stored procedure there is no need for a temporary table whatsoever, as execute immediate for example is capable of binding out parameters:
However I question the whole aproach of storing function names in tables and calling them with dynamic SQL itself, as it introduces a whole bunch of problems you don't have when simply calling procedures in a static fashion.
create or replace function get_some_stuff return number is retval number; begin execute immediate 'begin :outvar := my_func; end;' using out retval; return retval; end;
thanks for reply,
but the form builder gives this error: this feature is not supported in client side programs
execute immediate('select ' || v_func || ' from dual') into v_value;
the execute immediate should be wrapped into a db_procedure, the following is good solution to my problem,
thank you all,
CREATE OR REPLACE PROCEDURE PAYROLL.PROCESS_AD ( n_value in number, o_value out number) IS v_value number; V_FUNC VARCHAR2(100); V_PARAM VARCHAR2(100); begin select FUNCTION_NAME, PARAM INTO V_FUNC, V_PARAM from function_table where id = n_value; execute immediate('select ' || v_func || ' from dual') into v_value; o_value := v_value; end PROCESS_AD; /
This looks oddly similar to this thread posted 3 months ago: Dynamic sql from Forms 6 with return value
Storing a named function in a table and executing it when it is selected.... strange. But the other thread was asking the same thing.
The method is to create a stored procedure or function that uses Execute_Immediate to execute the function.
Here is the stored function posted in the other thread:
<pre> FUNCTION process(in_text varchar2) RETURN NUMBER IS
EXECUTE IMMEDIATE in_text USING OUT result;