Re: Mapping View Columns to Procedure/Function Parameters

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Tue, 12 Apr 2016 13:09:41 +0700
Message-ID: <CAP50yQ9_xvcxnSGmrtANFokDZXKTSuXtVM=Kv5wbNS4Z8+5eoQ_at_mail.gmail.com>

Come to think of it - pipelined table functions may work as well and involve less work than VPD.

select * from table(my_func('XXX','YYY','ZZZ'));

May also have to do some trickery with autonomous transactions. And perhaps it's worth mentioning that venturing down this road may not be the best solution. But it's possible.

On Tue, Apr 12, 2016 at 12:52 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> 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 - 08:09:41 CEST