Wednesday, October 19, 2011

How to pass parameters to 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;

No comments:

Post a Comment