Oracle View Parameters

To create a package:

Create or Replace package P_view_param is
function Set_param (num number) return number;
function Get_param return number;
End P_view_param;

To implement the package body:

Create or Replace package body P_view_param is
Paramvalue number;
function Set_param (num number) return number is
Begin
Paramvalue:=num;
return num;
End
function Get_param return number is
Begin
return paramvalue;
End
End P_view_param;

To create a view :

Create or replace view v_wms_inventory as
Select Material_no from [email protected]_db WHERE Quantity=p_view_param.get_param ()

Query sql:

SELECT * from V_wms_inventory where P_view_param.set_param (3050) = 3050;

Oracle View Parameters