creating parameterized views in oracle11g

The context method is described here: http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm

e.g. (example adapted from the above link)

CREATE CONTEXT dates_ctx USING set_dates_ctx_pkg;

CREATE OR REPLACE PACKAGE set_dates_ctx_pkg IS 
  PROCEDURE set(d1 in date, d2 in date); 
END; 
/

CREATE OR REPLACE PACKAGE BODY set_dates_ctx_pkg IS
  PROCEDURE set(d1 in date, d2 in date) IS 
  BEGIN 
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd1', TO_CHAR(d1,'DD-MON-YYYY'));
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd2', TO_CHAR(d2,'DD-MON-YYYY'));
  END;
END;
/

Then, set the dates in your application with:

BEGIN set_dates_ctx_pkg.set(mydate1, mydate2); END;
/

Then, query the parameters with:

SELECT bla FROM mytable
WHERE mydate
  BETWEEN TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd1')
          ,'DD-MON-YYYY')
      AND TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd2')
          ,'DD-MON-YYYY');

The advantage of this approach is that it is very query-friendly; it involves no DDL or DML at runtime, and therefore there are no transactions to worry about; and it is very fast because it involves no SQL – PL/SQL context switch.

Alternatively:

If the context method and John’s package variables method are not possible for you, another one is to insert the parameters into a table (e.g. a global temporary table, if you’re running the query in the same session), then join to that table from the view. The downside is that you now have to make sure you run some DML to insert the parameters whenever you want to run the query.