Re: Mapping View Columns to Procedure/Function Parameters

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Tue, 12 Apr 2016 10:13:27 +0800
Message-ID: <CABx0cSUcG4ebEC-RFizT1ZXwThFA+9secwX7hck37CfCBjGCdA_at_mail.gmail.com>

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.

>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 12 2016 - 04:13:27 CEST