Re: Mapping View Columns to Procedure/Function Parameters

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Tue, 12 Apr 2016 12:52:43 +0700
Message-ID: <CAP50yQ-dzCd5Pf4vQfLDbXvU09qrGmsEgv4YK62whYjckJ8+Wg_at_mail.gmail.com>

Have a look.at VPD - virtual private database. It can do that. On 12 Apr 2016 9:14 am, "Patrick Jolliffe" <jolliffe_at_gmail.com> wrote:

> 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 - 07:52:43 CEST