Forum Stats

  • 3,826,763 Users
  • 2,260,705 Discussions
  • 7,897,072 Comments

Discussions

Parameterized Views

2456

Comments

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Application contexts are certainly one way of injecting a runtime variable into a view. However they are hidden global variables, with all the disadvantages those give you, and in my experience they quickly become a huge pain once you have more than a few contexts unless there is a super-convenient way to set them. (You and I both query the same view but you get results back and I don't. I dig into the view's source code to find out why, find one or more application contexts and then I have to dig some more into application source code to find out how to set it - and if I don't have execute privilege on the trusted package, I can't.)

    The proposed syntax uses a local variable which is explicitly part of the query, would show in DESCRIBE and give errors if mandatory but null etc.

    ApexBineBEDEPeter Hraško
  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    I am not sure how it will help, we can simply expose the column to view and select with where clause...

    Also as we need to change the parameter we can change the value of where condition, right?

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    I am not sure how it will help, we can simply expose the column to view and select with where clause...

    Also as we need to change the parameter we can change the value of where condition, right?

    Maybe William Robertson's example (1 Dec) is a better use case. What do you think of it?

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge

    maybe you can use this example to implement what you want to do already?

    create table Blume as

    select rownum rn, dbms_random.value num

    from

            dual

    connect by

            level <=1000;

    --  grant EXECUTE on DBMS_SESSION to &user;

    --  grant create any context to &user;

    -- some code borrowed from http://www.adp-gmbh.ch/ora/sql/create_context.html

    CREATE CONTEXT BIENE using Wiese;

    create or replace package Wiese as

        procedure set_value_in_context(some_value in varchar2);

    end Wiese;

    /

    create or replace package body Wiese as

        procedure set_value_in_context(some_value in varchar2) is

        begin

            dbms_session.set_context('BIENE', 'random_filter', some_value);

        end set_value_in_context;

    end Wiese;

    /

    create view Rasenmaeher as

    select count(*) Bluemchen

    from Blume b

    where b.num >

         to_number(sys_context('BIENE', 'random_filter') );

    exec Wiese.set_value_in_context(to_char(42/100));

    select sys_context('BIENE', 'random_filter') from dual;

    select * from Rasenmaeher;

    -- 572 in my testcase

    exec Wiese.set_value_in_context(to_char(99/100));

    select * from Rasenmaeher;

    -- 4 in my testcase

    Thanks Martin,

    depending on the requirements this might be a very good solution.

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    Apart from William example, this could be useful in highly complex query having 2-3 levels of nesting, grouping and more. I need to deal with it in our last development cycle. I am voting up

    ApexBinePeter Hraško
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    We can do this already using (pipelined) table functions.

    Since SQL so far has no real concept of parameters (the CBO does, but not the language itself), I feel we are missing something here.

    The standard solution should be to expose the "parameter" as a column and then filter on that column (as William mentioned).

    For cases where this is syntactically or performancewise not easy to do, there are some alternatives (contexts and PTFs).

    In this specific example, I don't even feel the need for grouping. The result is only one row (for one ISIN). So as long as there is a filter on ISIN and on the PRICE_DATE, we could do this (12c)

    SELECT isin, 
      price_date AS price_date,
      price      AS price
    FROM share_price
    WHERE price_date <= :date
    and isin = :X
    order by price_date desc
    fetch first row only;
    

    for multiple ISIN (groups) we need to fallback to the ROW_NUMBER solution (fetch first is internally transformed into such a query).

    select * from (
       SELECT isin, 
         price_date AS price_date,
         price      AS price,
         row_number () over (parition by isin order by price_date desc) rn
       FROM share_price
       WHERE price_date <= :date 
       and isin IN ( :X, :Y, :Z)
      )
    where rn = 1 ;
    

    That can't be as easily added to a view anymore. In such a case I would go the PTF route.

    jnicholas330gassenmj
  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy

    As already pointed out, I would generally create pipelined function in case if I need something like parameterized view for complex query. However, I would love if Oracle provides it our of the box. Voted Up!

    Regards,

    Hari

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Another example I came up against recently is a schema where everything is versioned by business date range. (Also by valid as-at range, but I'll spare you the full horror.) As they are defined as ranges rather than simple "business date" columns, you have to know what date you are querying for in order to join anything. Now we have been given the task of writing a set of views for a data extraction interface. Options are:

    1. Expose all of the date ranges for the source tables and let users add their own filters.
    2. Define an application context (requiring a procedural step prior to querying).
    3. Populate a temp table and join to that within the views (requiring a procedural step prior to querying).
    4. Have the views join to an existing system table (limiting all queries to the system's "current" business date).
    5. Replace the views with pipelined functions. As a PL/SQL guy I like this, though users or applications would have to use queries along lines of "select x, y, z from table(somepackage.somefunction(date '2016-05-01'))" instead of the ordinary views they may be expecting, and I expect many third party products will choke on this syntax, and users lose the ability to browse a list of available views. (I suppose third party tools would also take a while to support a new parameterised view syntax, but I expect they would manage it eventually.)

    Possibly native SQL support for time series data is what we really need here, but that's for a whole other Idea.

    Thorsten Kettner
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jul 27, 2017 8:42AM

    Another example I came up against recently is a schema where everything is versioned by business date range. (Also by valid as-at range, but I'll spare you the full horror.) As they are defined as ranges rather than simple "business date" columns, you have to know what date you are querying for in order to join anything. Now we have been given the task of writing a set of views for a data extraction interface. Options are:

    1. Expose all of the date ranges for the source tables and let users add their own filters.
    2. Define an application context (requiring a procedural step prior to querying).
    3. Populate a temp table and join to that within the views (requiring a procedural step prior to querying).
    4. Have the views join to an existing system table (limiting all queries to the system's "current" business date).
    5. Replace the views with pipelined functions. As a PL/SQL guy I like this, though users or applications would have to use queries along lines of "select x, y, z from table(somepackage.somefunction(date '2016-05-01'))" instead of the ordinary views they may be expecting, and I expect many third party products will choke on this syntax, and users lose the ability to browse a list of available views. (I suppose third party tools would also take a while to support a new parameterised view syntax, but I expect they would manage it eventually.)

    Possibly native SQL support for time series data is what we really need here, but that's for a whole other Idea.

    William Robertson wrote: ...Possibly native SQL support for time series data is what we really need here, but that's for a whole other Idea.

    I would support that idea!

    The whole discussion about sql:assert made me think that issues around "timed data" are one of the main culprits to solve. Its something that simple moddeling and simple constraints do not handle effectivly yet.

    William Robertson
  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    SQL Server (the second best RDBMS ) already has them. They're called inline table valued functions. We shouldn't fall behind SQL Server...

    ApexBinePeter Hraško