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.

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.

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.

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.

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

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?

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

where c2 = ...

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

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

when passed the date values explicit, working fine.
but when passed as sysdate, it is not working fine,

Parameters should not be used in the body of sql_macro itself, other than dbms_tf.table_t and dbms_tf.columns_t. The body is executed only on hardparse, the runtime values of columns, variables and functions (sysdate) are not available.

The strangeness of the implementation is that the number and date13 literals are available in the body of the function, but varchar2 and date12 are not.

I'm unclear what you mean by date12/13

Internal date format is 12 or 13 bytes.

SQL> select dump(date '1-1-1') d13, dump(cast(date '1-1-1' as date)) d12 from dual;

                             D13                                D12
________________________________ __________________________________
Typ=13 Len=8: 1,0,1,1,0,0,0,0    Typ=12 Len=7: 100,101,1,1,1,1,1

SQL> with function fu21c(
  2    s varchar2, c clob, vs ku$_vcnt,
  3    d13 date, d12 date,
  4    r raw, n number
  5  ) return varchar2 sql_macro as
  6    function q(l varchar2, v varchar2) return varchar2 as
  7    begin
  8      return 'select '''||replace(l, '''', '''''')||''' par, '''
  9                        ||replace(v, '''', '''''')||''' val from dual
 10  ';
 11    end;
 12  begin
 13    return           q('varchar2', s)
 14    ||'union all '|| q('clob',     to_char(c))
 15    ||'union all '|| q('ku$_vcnt', vs(1))
 16    ||'union all '|| q('date13', to_char(d13,'yyyy-mm-dd'))
 17    ||'union all '|| q('date12', to_char(d12,'yyyy-mm-dd'))
 18    ||'union all '|| q('raw',    rawtohex(r))
 19    ||'union all '|| q('number', to_char(n));
 20  end;
 21  select *
 22  from fu21c('str1', to_clob('str2'), ku$_vcnt('str3'),
 23             date '1-1-1', cast(date '1-1-1' as date),
 24             hextoraw('414243'), 123);
 25* /

        PAR           VAL
___________ _____________
varchar2
clob
ku$_vcnt    str3
date13      0001-01-01
date12
raw         414243
number      123

Hi Chris ,

Can i use DBMS_RLS kage to add Dynamic Policy to view that will acts like dynamic where condition in view ?

Kindly .. Explain this feature

Thanks

hi Chris,
I followed the tutorial you provided ( https://livesql.oracle.com/apex/livesql/file/tutorial_KQNYERE8ZF07EZMRR6KJ0RNIR.html )

i have created a SQL Macro :

create or replace FUNCTION FAILURES_SUMMARY(p_periode_start_date DATE DEFAULT trunc(sysdate , 'MM')
    , p_periode_end_date   DATE default sysdate) 
                   RETURN clob SQL_MACRO as
BEGIN
  RETURN q'{

SELECT DISTINCT
    u.equipment_id
    , u.operation_type_id
    , ot.operation_type
    , ot.operation_nature_id
    , opn.OPERATION_NATURE_NAME AS operation_nature
    , vt.equipment_name
    , vt.machine_id
    , vt.machine_name
    , vt.machine_nature
    , CASE
      WHEN vt.machine_nature = 1 THEN
      'Turbo Alternateurs'
      ELSE
      'Machine Principales'
      END AS machine_nature_name
    , mop.ouvrage
    , vt.ppl_category_id
    , pc.ppl_category_type as ppl_category_name
    , u.unavailibility_report_date
    , availibility_date
    , nvl(
        COUNT(*)
        OVER(PARTITION BY vt.pipleline_code, pc.ppl_category_type, vt.machine_nature ), 0
    )   AS tot_failures
    ,nvl(
        COUNT(*)
        OVER(PARTITION BY vt.pipleline_code, pc.ppl_category_type, vt.machine_nature, u.operation_type_id), 0
    )   AS tot_failures_by_operation_nature
    --, periode_length(u.unavailibility_report_date, p_periode_start_date, p_periode_end_date)
    , CASE
      WHEN unavailibility_report_date > trunc(
          sysdate, 'MM'
      ) THEN
      round(
          sysdate - unavailibility_report_date, 0
      )
      ELSE
      round(
          sysdate - trunc(
              sysdate, 'MM'
          ), 0
      )
      END AS tot_failure_days
FROM
    unavailibility_reporting   u
    LEFT JOIN mnt_tree                   vt ON u.equipment_id = vt.equipment_id
    LEFT JOIN PIPELINE_CATEGORIES        pc ON pc.ppl_category_id = vt.ppl_category_id
    LEFT JOIN machine_operating_pipeline mop ON vt.machine_id = mop.machine_id
    LEFT JOIN operations_types           ot ON ot.operation_type_id = u.operation_type_id
    left join operation_natures          opn on ot.OPERATION_NATURE_ID = opn.OPERATION_NATURE_ID
  }' ;
  
END;

But when running macro FAILURES_SUMMARY using the following query:

select * from FAILURES_SUMMARY(p_periode_start_date => to_date('14/11/2023','dd/mm/yyyy')
                               ,p_periode_end_date => to_date('10/11/2023','dd/mm/yyyy'));

i get the below error:

ORA-22905: cannot access rows from a non-nested table item
22905. 00000 - "cannot access rows from a non-nested table item"
*Cause: attempt to access rows of an item whose type is not known at
parse time or that is not of a nested table type
*Action: use CAST to cast the item to a nested table type
Error at Line: 1 Column: 15