Oracle Application dicussion and support forum

Parameterized views in Oracle. It is not possible to create Parameterized Views in Oracle. In order to create Parameterized one has to make use of some work around like sys_context.

create context params using setmm;

create package setmm is
procedure minmax(vmin in number, vmax in number);
end setmm;
/

create package body setmm is
procedure minmax(vmin in number, vmax in number) is
begin
dbms_session.set_context(
namespace => ‘params’, attribute => ‘minval’, value => vmin);
dbms_session.set_context(
namespace => ‘params’, attribute => ‘maxval’, value => vmax);
end minmax;
end setmm;
/

create view parametrized as
  select * from (select level n from dual connect by level <= 1000)
  where n between to_number(sys_context(‘params’, ‘minval’))

and to_number(sys_context(‘params’, ‘maxval’));

SQL> exec setmm.minmax(vmin => 5, vmax => 14)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select * from parametrized;
5
6
7
8
9
10
11
12
13
14

SQL> exec setmm.minmax(vmin => 2, vmax => 4)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select * from parametrized;
2
3
4