Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ankit.

Asked: August 10, 2017 - 7:48 am UTC

Last updated: January 19, 2021 - 1:09 pm UTC

Version: 12.1.0

Viewed 50K+ times! This question is

You Asked

Hi,

how to create view with parameter?

we want to create view definition as below,

create view test_item_v
as 
select item_class,
nvl(rev_id,v_rev_id),
sum(total_cost),
sum(total_resale),
sum(margin)
from test_item_class
where rev_id = v_rev_id;


Please suggest.

Thanks.

and we said...

You can't. Adding parameterized views is one of the more popular suggestions on the Database Ideas forum:

https://community.oracle.com/ideas/11316

You can get close with sys_context. Though you need to call this to set the value before querying the view:

create table t (
  x int
);
insert into t 
  select rownum x from dual
  connect by level <= 10;

commit;

create or replace context ctx using ctx_api;

create or replace package ctx_api as

  procedure set_filter(val in varchar2);
  
end ctx_api;
/

create or replace package body ctx_api is

  procedure set_filter(val in varchar2) is
  begin
    dbms_session.set_context('CTX', 'filter', val);
  end set_filter;

end ctx_api;
/

create or replace view vw as 
  select * from t
  where  x = sys_context('CTX', 'filter');
  
exec ctx_api.set_filter(1);

select * from vw;

X  
1  

exec ctx_api.set_filter(2);

select * from vw;

X  
2  


And it won't work in all situations. https://dba.stackexchange.com/q/21644/2264

Or you may be able to work around it using (temporary) tables. Though again, this requires you to initialize the data first and may not always be appropriate:

create global temporary table tmp (
  x int
);

create or replace view vw as 
  select * from t
  where  t.x = (select tmp.x from tmp);
  
insert into tmp values (3);
select * from vw;

X  
3  

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Followup

Ankit Bhatt, August 10, 2017 - 10:59 am UTC

Thanks a lot Chris. very helpful snippets but our issue is that we are passing rev_id at run time, so for that can we use sys_context? please suggest.
Chris Saxon
August 10, 2017 - 1:49 pm UTC

You have to set the context before querying the view. If you can't do that, this won't work for you.

followup on last comments

Ankit Bhatt, August 10, 2017 - 4:29 pm UTC

Thanks again. in the same view, we are joining 2 tables and creating a view and for those table definitions are as under,

create table test_item_class(calc_abc varchar2(10),rev_id number,cost number,resale number,margin number)

/

create table test_item_details(item_class varchar2(10), item_desc varchar2(50))

/



insert into test_item_class values ('A',1,1000,500,300)

/

insert into test_item_class values ('B',1,2000,400,200)

/

insert into test_item_class values (null,1,3000,300,100)

/

insert into test_item_class values ('C',2,1200,500,300)

/

insert into test_item_class values ('D',2,1500,500,200)

/



insert into test_item_details values ('A','Desc_A')

/

insert into test_item_details values ('B','Desc_B')

/

insert into test_item_details values ('C','Desc_C')

/

insert into test_item_details values ('D','Desc_D')

/

insert into test_item_details values ('A1A','Desc_A1A')

/

insert into test_item_details values ('A1B','Desc_A1B')

/

View:

CREATE VIEW test_item_v AS
SELECT
a.item_class,
a.rev_id,
SUM(a.cost) cost,
SUM(a.resale) resale,
SUM(a.margin) margin
FROM
(
SELECT
tic.calc_abc item_class,
tic.rev_id,
COUNT(DISTINCT tic.cost) cost,
COUNT(DISTINCT tic.resale) resale,
COUNT(DISTINCT tic.margin) margin
FROM
test_item_class tic
RIGHT OUTER JOIN test_item_details tid ON tid.item_class = tic.calc_abc
GROUP BY
tic.calc_abc,
tic.rev_id
) a
GROUP BY
a.item_class,
a.rev_id;

Now we want output as,

item_class | rev_id | Cost | resale | Margin

A | 1 | 1 | 1 | 1

B | 1 | 1 | 1 | 1

C | 1| NULL|NULL|NULL

D | 1 | NULL|NULL|NULL

A1A | 1 | NULL|NULL|NULL

A1B | 1 | NULL|NULL|NULL

Is it possible to get with the given details?

Thanks.

Chris Saxon
August 11, 2017 - 2:49 pm UTC

Hmmm. I don't think so. Querying rev_id in the view means you inner joining test_item_class instead of outer joining it. So you end up removing all rows except A & B.

I think you need to do this in the subquery which is inside the view.

Add column to filter to view or use table function

Evan, August 10, 2017 - 6:40 pm UTC

When I've run into this problem, what I've usually done is added the column I want to have as a parameter to the view and then when I select from the view I have a condition in the WHERE clause on the column.

So in this example, add rev_id to the view e.g.

create view test_item_v
as
select rev_id,
item_class,
sum(total_cost),
sum(total_resale),
sum(margin)
from test_item_class;
--I think you'll need to add a group by clause since there are aggregate functions

and then when using the view

select *
from test_item_v
where rev_id = v_rev_id;


Another option is to create a PL/SQL table function with a parameter for the column you want to parameterize. Then execute the SQL statement using the parameter passed in. Finally, return the result set.
Chris Saxon
August 11, 2017 - 2:39 pm UTC

Yep, those are worth investigating.

RE: Add column to filter...

Duke Ganote, August 11, 2017 - 4:52 pm UTC

One potential issue with adding a column to the view, then filtering on the results, is an "unpushed" predicate. For small data sets and/or a simple view-query (where the optimizer can transform the query in order to push the predicate), it's not important.

However for large data sets, it's the difference between the first SELECT (which mimics a view with a column/parameter) and the second:

SQL> exec DBMS_application_info.set_client_info('SYS'); -- here's the parameter I want. Owner = 'SYS'

PL/SQL procedure successfully completed.

SQL> select cnt from ( select owner, count(*) cnt from dba_source group by owner ) where owner = userenv('client_info');

         CNT
------------
     186,077

SQL> select count(*) cnt from dba_source where owner = userenv('client_info');

         CNT
------------
     186,077


Same results, but for a complex query or very large data set, the difference in execution time could be significant.
Chris Saxon
August 14, 2017 - 2:27 pm UTC

Yep, if the optimizer can't push the predicate for some reason the SQL could be too slow.

SQL table macros

Stew Ashton, January 19, 2021 - 10:21 am UTC

Now that SQL table macros are available in the current long-term support release (19c), here is the macro equivalent of a parameterized view.

First, a tiny test table:
drop table test_item_class purge;
create table test_item_class(item_class, rev_id, total_cost, total_resale, margin) as
select 'A', 1, 100, 90, 10 from dual;

Next, a view definition that might actually work if parameters were allowed:
create view test_item_v as 
select item_class,
rev_id,
sum(total_cost) sum_cost,
sum(total_resale) sum_resale,
sum(margin) sum_margin
from test_item_class
where rev_id = v_rev_id
group by item_class, rev_id;
Now, instead of preceding the SELECT statement by "create view", we have to create a function. The code that wraps the SELECT part is a few lines more, but that's it.
create or replace function test_item_v(v_rev_id in number) return varchar2 sql_macro is
begin
  return '
select item_class,
rev_id,
sum(total_cost) sum_cost,
sum(total_resale) sum_resale,
sum(margin) sum_margin
from test_item_class
where rev_id = v_rev_id
group by item_class, rev_id
';
end test_item_v;
/
select * from test_item_v(1);

I     REV_ID   SUM_COST SUM_RESALE SUM_MARGIN
- ---------- ---------- ---------- ----------
A          1        100         90         10
Best regards, Stew
Chris Saxon
January 19, 2021 - 1:09 pm UTC

Great point Stew

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.