Tom, Thank you so much for the response. As always, extremely helpful.

Hi

I have used a couple of alternate approaches to the parameterized view.

Using session context might not always be appropriate in a situation with connection pools perhaps? I am thinking that if you fail to set context you might get the last user of the connection's parameters.

Alternate 1) Create a Global Temporary Table as ON COMMIT DELETE ROWS to contain parameter values. This table can be referenced in the view SQL to achieve the desired subsetting - as simple or complex as you like. The 'parameters' will then have transaction only scope.

Alternate 2) Pretty much the same as Toms's suggestion to have a Stored Proc returning a Ref Cursor, but I sometimes use a PIPELINED function instead, as this supports even the dumbest of clients. Some clients are not capable of handling Ref Cursors at all, or handle them badly (in my experience).

I think it's about time we did away with the need for this silly SYS_CONTEXT rubbish.

Oracle should introduce proper parametrised...parameterised...parametarised... Oh damn it! - views with parameters - so as to conform to everyone's expectations.

For example,

CREATE OR REPLACE VIEW VIEW_ABC (parm1 INTEGER, parm2 DATE) AS
  SELECT *
  FROM   XYZ
  WHERE  EXISTS (SELECT 'X'
                 FROM   QPR
                 WHERE  QPR.JOIN_COLUMN_1 = XYZ.JOIN_COLUMN_1
                 AND    QPR.ATTR_COLUMN_1 = (parm1)
                 AND    QPR.ATTR_COLUMN_2 = (parm2))
/

Then, all we need do is,

SELECT *
FROM   VIEW_ABC (101, SYSDATE)
/

No probs. with bind variables. Nicely integrated as one would expect.

The prettiest approach to creating a parameterized view (almost) is to create a pl/sql function that returns an Oracle collection type. Then query the 'view' with a query such as

SELECT *
FROM TABLE(mycollectionfunction(parameter list));

This requires defining CREATE TYPEs first. Here is my simple code in an HR application to find all the departments a position (appointment) is works for as of a given date.

CREATE OR REPLACE
type APPTDEPT_TY is object (appt_department varchar2(11));

CREATE OR REPLACE
type APPTDEPT_TBL_TY is table of apptdept_ty;

CREATE FUNCTION q_apptdept (p_appointment_id varchar2, p_date date)
RETURN apptdept_tbl_ty
IS
CURSOR cur (c_appt_id varchar2, c_date date)
IS
SELECT d.appt_department
FROM apptdept d
WHERE D.APPOINTMENT_ID = c_appt_id
AND D.DEPT_EFF_DATE <= c_date
AND (D.DEPT_END_DATE IS NULL
OR D.DEPT_END_DATE >= c_date);

ty apptdept_ty;
tbl apptdept_tbl_ty := apptdept_tbl_ty ();
BEGIN
FOR rec IN cur (p_appointment_id, p_date) LOOP
ty := apptdept_ty (rec.appt_department);
tbl.EXTEND;
tbl (tbl.LAST) := ty;
END LOOP;

RETURN tbl;
END;

Now, we can execute the query

SELECT *
FROM TABLE(Q_APPTDEPT('00123456',SYSDATE));

to get all departments for appoinmtent '00123456' that are active as of today.

CREATE FUNCTION q_apptdept (p_appointment_id varchar2, p_date date)
<b>PIPELINED</b>
      RETURN apptdept_tbl_ty
   IS
      CURSOR cur (c_appt_id varchar2, c_date date)
      IS
         SELECT   d.appt_department
           FROM   apptdept d
          WHERE   D.APPOINTMENT_ID = c_appt_id
              AND D.DEPT_EFF_DATE <= c_date
              AND (D.DEPT_END_DATE IS NULL
                OR D.DEPT_END_DATE >= c_date);
   BEGIN
      FOR rec IN cur (p_appointment_id, p_date) 
      LOOP
         <b>pipe row( apptdept_ty (rec.appt_department) )</b>;
      END LOOP;
<b>
      RETURN;</b>
   END;

Tom,
In your very first reply to the original poster, you said that you wouldn't consider a parameterized view. What if you had a situation where you had tables that held data for different companies and you wanted to let user's only see the data for their specific company. Without going for a full-blown FGAC implementation, i thought the easiest way to do this was to do a
SELECT <col>
FROM <table_name>
WHERE <conditions>
AND company_id = sys_context('my_ctx', 'company_id);
Would you not use a view with parameters this way either? Since the same SQL could be used in different places, wouldn't it make sense to just have it defined as a view once? Thank you, Chandini

is this new to 19c or has this always worked this way? it solves the problem of parameterizing a view

with local context params and controlling package params_api
package body params_api is
function set_parameter (n in varchar2,v in varchar2) return number is
begin
dbms_session.set_context('params',n,v);
return 1;
end;
end;

-- this single query sets then selects a local context variable

with a as (select params_api .set_parameter('v1','value1') p_set from dual) select sys_context('params','v1') from dual,a where a.p_set = 1;