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: July 26, 2021 - 3:31 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

  (7 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

Why use SQL Macro Functions?

Stew Stryker, May 25, 2021 - 9:16 pm UTC

Thanks to Stew Ashton for pointing out that you can use the new SQL Macro Function capabilities to create the equivalent of a view that accepts a parameter.

Pardon my short-sightedness, but why bother to do this? How is this better than having a view, then adding a WHERE clause on one or many fields in the view?
Chris Saxon
May 26, 2021 - 1:21 pm UTC

If you have an inline view like this:

select * from (
  select c1, count(*)
  from   ...
  group by c1
)


You can't add

where c2 = ...


To the outer query because c2 is only present in the inner query, you need to push it inside the inline view like this:

select * from (
  select c1, count(*)
  from   ...
  where c2 = ...
  group by c1
)


A regular view is the same principle - there are many cases where filtering outside the view is impossible or gives different results to filtering inside it

sysdate on sql macros

Rajeshwaran, Jeyabal, July 26, 2021 - 3:04 pm UTC

Team,

in the below sql macro included the call to dbms_output for debugging purpose.
when passed the date values explicit, working fine.
but when passed as sysdate, it is not working fine, could you please help us to get this understand better.

demo@QES1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0


demo@QES1> create or replace function date_range( p_from_dt in date,
  2     p_to_dt in date )
  3  return varchar2
  4  sql_macro is
  5     l_sql long;
  6  begin
  7     dbms_output.put_line(' from_dt = '|| to_char(p_from_dt,'dd-mon-yyyy') ||
  8             ' to_Dt = '|| to_char(p_to_dt,'dd-mon-yyyy') );
  9
 10     l_sql := q'# with date_range( start_dt, end_dt ) as (
 11                                     select to_date('XXX','dd-mon-yyyy') as start_dt,
 12                                             to_date('YYY','dd-mon-yyyy') as end_dt
 13                                     from dual
 14                                     union all
 15                                     select start_dt+1, end_dt
 16                                     from date_range
 17                                     where start_dt+1 <= end_dt )
 18                             select  start_dt from date_range #';
 19     l_sql := replace(l_sql,'XXX',to_char(p_from_dt,'dd-mon-yyyy') );
 20     l_sql := replace(l_sql,'YYY',to_char(p_to_dt,'dd-mon-yyyy') );
 21
 22     dbms_output.put_line(l_sql);
 23     return l_sql;
 24  end;
 25  /

Function created.

demo@QES1> select * from date_range( to_date('01-jul-2021','dd-mon-yyyy'),
  2     to_date('03-jul-2021','dd-mon-yyyy') );

START_DT
-----------
01-JUL-2021
02-JUL-2021
03-JUL-2021

 from_dt = 01-jul-2021 to_Dt = 03-jul-2021
 with date_range( start_dt, end_dt ) as (
                                        select to_date('01-jul-2021','dd-mon-yyyy') as start_dt,
                                                to_date('03-jul-2021','dd-mon-yyyy') as end_dt
                                        from dual
                                        union all
                                        select start_dt+1, end_dt
                                        from date_range
                                        where start_dt+1 <= end_dt )
                                select  start_dt from date_range
demo@QES1> select * from date_range( sysdate, sysdate+3 );

START_DT
-----------


 from_dt =  to_Dt =
 with date_range( start_dt, end_dt ) as (
                                        select to_date('','dd-mon-yyyy') as start_dt,
                                                to_date('','dd-mon-yyyy') as end_dt
                                        from dual
                                        union all
                                        select start_dt+1, end_dt
                                        from date_range
                                        where start_dt+1 <= end_dt )
                                select  start_dt from date_range

Chris Saxon
July 26, 2021 - 3:31 pm UTC

This looks suspiciously like an example from a recent post by Jonathan Lewis:

https://jonathanlewis.wordpress.com/2021/07/22/sql-macro/

As he notes there, all the date replacement, to_char, etc. is unnecessary. You use the parameter names directly as-is the text of the macro you return. The problem is there's currently a bug when using the WITH clause.

More to Explore

Design

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