Application is JDEdwards Enterprise One. Yes I realize we could write a
Business Function in C to perform what we want, but I just want to see if
it is possible to do it this way.
Realize maybe I can get pretty much what I want by an instead of insert
trigger, see a simple implementation below. However I am still struggling
to understand if and how it would be possible to map a simple select
against a view with predicates defining procedure and function, to passing
those predicates value in as parameters to a stored procedure so I can use
then to generate dynamic SQL.
SQL>
SQL> SET DEFINE
OFF
SQL> SET SERVEROUTPUT
ON
SQL> create procedure a(param1 in varchar2)
as
2
begin
3 dbms_output.put_line('Proc
a:'||param1);
4
end;
5
/
Procedure
created.
SQL> create procedure b(param1 in varchar2)
as
2
begin
3 dbms_output.put_line('Proc
b:'||param1);
4
end;
5
/
Procedure
created.
SQL>
SQL> create table dummy_table (procedure_name varchar2(30), parameter_value
varchar2(4000));
Table
created.
SQL>
SQL> create view procedure_view as select procedure_name, parameter_value
from
dummy_table;
View
created.
SQL>
SQL> create or replace trigger procedure_trigger instead of insert on
procedure_view for each
row
2
begin
3 execute immediate 'BEGIN ' || :NEW.PROCEDURE_NAME || '(:1); END;'
USING
:NEW.PARAMETER_VALUE;
4
END;
5
/
Trigger
created.
SQL>
SQL> INSERT INTO PROCEDURE_VIEW (PROCEDURE_NAME, PARAMETER_VALUE) VALUES
('A',
'P1');
Proc
a:P1
1 row
created.
SQL>
SQL> INSERT INTO PROCEDURE_VIEW (PROCEDURE_NAME, PARAMETER_VALUE) VALUES
('B',
'P1');
Proc
b:P1
1 row created.
