How to pass parameter to a oracle view

How to pass parameter to a oracle view
========================================

you can't design a view that automatically asks the user for parameters at run time.

You can have a view based on parameters, which can be stored in a number of ways, including
(1) SYS_CONTEXT variables
(2) GLOBAL TEMPORARY TABLE
(3) Package variable
(4) Regular table
(5) With a Function (pipeline function is better)

The user has to remember to set the parameters before using the view.


1. First create view like follow
---------------------------------
CREATE OR REPLACE VIEW scott.test_parameter_view_date
AS
SELECT *
FROM emp
WHERE hiredate = TO_DATE (USERENV ('client_info'), 'dd-mon-yyyy');

--to_char(timstamp,'ddmmrrrr') =userenv('client_info')
--To_Date(SYS_CONTEXT ('userenv','client_info'),'dd-mon-yyyy')


2. then execute the following ...
--------------------------------

exec dbms_application_info.set_client_info('22-Feb-1981');


3. Done your view , now select the view
----------------------------------------

SELECT * FROM scott.test_parameter_view_date;


Note:-
======
You can also use dbms_session.set_context in 8i and up to
greatly increase the namespace/number of parameters you can
pass and reference via the SYS_CONTEXT() functions.


==================Another Example===============================

On remote db
================
REMOTEDB> create view myview
as select * from dual
where sys_context ('userenv', 'client_info') = 1

View created.


REMOTEDB> grant select on myview to public
/

Grant succeeded.


On LOCAL DB:
===============

DEV> exec dbms_application_info.set_client_info@REMOTEDB(1)

PL/SQL procedure successfully completed.

DEV> select DUMMY from test_schema.myview@REMOTEDB
/
-----------
X



3) example of package variable



CREATE TABLE CITIES
(
REGION VARCHAR2 (30),
COUNRTY VARCHAR2 (30),
CITY VARCHAR2 (30)
);

Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATED','NEW YORK');

Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','FRANCE','PARIS');

Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','JAPAN','TOKYO');

Insert into CITIES (REGION,COUNRTY,CITY) values ('ASIA','INDIA','MUMBAI');

Insert into CITIES (REGION,COUNRTY,CITY) values ('EUROPE','UNITED KINGDOM','LONDON');

Insert into CITIES (REGION,COUNRTY,CITY) values ('AMERICA','UNITED STATES','WASHINGTON DC');

COMMIT;

CREATE OR REPLACE PACKAGE PKG_PARAM AS
PROCEDURE SET_REGION (P_REGION IN VARCHAR2);
FUNCTION GET_REGION RETURN VARCHAR2;
END PKG_PARAM;
/

CREATE OR REPLACE PACKAGE BODY PKG_PARAM AS
REGION VARCHAR2 (30);
PROCEDURE SET_REGION (P_REGION IN VARCHAR2)
IS
BEGIN
REGION := P_REGION;
END;
FUNCTION GET_REGION RETURN VARCHAR2
IS
BEGIN
RETURN REGION;
END;
END PKG_PARAM;
/

CREATE OR REPLACE VIEW PARAM_VIEW AS
SELECT *
FROM CITIES
WHERE REGION=PKG_PARAM.GET_REGION;

SELECT * FROM PARAM_VIEW;


EXEC PKG_PARAM.SET_REGION('ASIA');


SELECT * FROM PARAM_VIEW;



==========================================================
Multiple parametrized view in oracle with package variable
==========================================================


CREATE OR REPLACE PACKAGE pkg_parameter_in_view
AS
PROCEDURE set_parameter (
p_brancd IN VARCHAR2,
p_actype IN VARCHAR2,
p_actnum IN VARCHAR2
);

FUNCTION get_parameter_brancd
RETURN VARCHAR2;

FUNCTION get_parameter_actype
RETURN VARCHAR2;

FUNCTION get_parameter_actnum
RETURN VARCHAR2;
END pkg_parameter_in_view;
/




CREATE OR REPLACE PACKAGE BODY pkg_parameter_in_view
AS
s_brancd VARCHAR2 (30);
s_actype VARCHAR2 (50);
s_actnum VARCHAR2 (50);

PROCEDURE set_parameter (
p_brancd IN VARCHAR2,
p_actype IN VARCHAR2,
p_actnum IN VARCHAR2
)
IS
BEGIN
s_brancd := p_brancd;
s_actype := p_actype;
s_actnum := p_actnum;
END;

FUNCTION get_parameter_brancd
RETURN VARCHAR2
IS
BEGIN
RETURN s_brancd;
END;

FUNCTION get_parameter_actype
RETURN VARCHAR2
IS
BEGIN
RETURN s_actype;
END;

FUNCTION get_parameter_actnum
RETURN VARCHAR2
IS
BEGIN
RETURN s_actnum;
END;
END pkg_parameter_in_view;
/



CREATE OR REPLACE FORCE VIEW atmutl.vw_account_transaction_test (brancd,
doctyp,
docnum
)
AS
SELECT brancd, doctyp, docnum
FROM test.act_master
WHERE brancd = pkg_parameter_in_view.get_parameter_brancd
AND actype = pkg_parameter_in_view.get_parameter_actype
AND actnum = pkg_parameter_in_view.get_parameter_actnum
/


select * from test.vw_account_transaction_test;


EXEC test.PKG_PARAMeter_in_view.SET_parameter('333','S01','333456789333') ;



select * from test.vw_account_transaction_test ;




4) example of Regular table
=====================


We created a little table that had criteria in it that could be changed on occasion. The table only had 1 row on it. The view joined to the table and used the fields.

Lets just say we called it Parm_Tbl. It had a few fields that served as controls to the view

select col1, col2, col3
from tableX, parm_tbl
where col5 = Parm_tbl.var1
and col6 = Parm_tbl.var2

This may or may not help depending on how often the variables need to change.




Following are the advantages of parameterized views.
=====================================================

1. The biggest advantage is that it allows you to
join the view with other tables/views thus
allowing you to write set based code. An approach
using procedure that returns a ref cursor does not
allow this and I found that to be a major limitation.
This of course assumes that you want to write the
view with supplying the values at run time (i.e.
write a parameterized view.)
2. In corner cases, it may help performance as you
mentioned above (in rare cases when optimizer is
not able to merge.)

Cons:
1. It is a painful thing to set the context values
each time before testing. If your views were not
parameterized you can run simple selects from the
views (e.g. select * from v1) which of course
parameterized views dont allow untill you run
the context setting code.

Overall, I felt that in general, one is better off not
using them - use straight forward views. In case of
performance issues, try the param views out. But dont
start out with it in the first place if your needs
are fulfilled without them (Which would be the case
most of the time.)