Solved

create a parametrized view

Posted on 2012-03-19
10
291 Views
Last Modified: 2012-06-27
Hi Oracle Gurus

I have a view created in oracle that it runs from bigger base tables and user who is going to consume the data wants to filter that view based on start dates like '01-JAN-2012' or '01-FEB-2012' for their analysis - how would I get the user input start date and filter the record using that date - I did modify the view to run the start dates for them everytime but since they will be using this often they wanted to use it as parameter to pass and get the results.
0
Comment
Question by:mahjag
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 122 total points
Comment Utility
>>they wanted to use it as parameter to pass and get the results

You cannot.

Just create an inline view.  If you have a view called MyView

if you have: select col1, col2 from MyView,SomeOtherTable where ...

Change it to: select col1, col2 from (select * from MyView where date_col between sysdate-1 and sysdate) MyView,SomeOtherTable where ...

substitute sysdate with your date values.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 123 total points
Comment Utility
You could base the view on system contexts,  or package functions

Doing that, you would set values first, then query the views.

for example.


CREATE OR REPLACE PACKAGE my_view_pkg
IS
    PROCEDURE set_my_date(p_date IN DATE);

    FUNCTION get_my_date
        RETURN DATE;
END;

CREATE OR REPLACE PACKAGE BODY my_view_pkg
IS
    g_date   DATE;

    PROCEDURE set_my_date(p_date IN DATE)
    IS
    BEGIN
        g_date  := p_date;
    END;

    FUNCTION get_my_date
        RETURN DATE
    IS
    BEGIN
        RETURN g_date;
    END;
END;

CREATE OR REPLACE VIEW recently_modified_objects
AS
    SELECT *
      FROM all_objects
     WHERE last_ddl_time >= my_view_pkg.get_my_date;


BEGIN
    -- find objects modified in the last 2 weeks
    my_view_pkg.set_my_date(SYSDATE - 14);
END;


select * from recently_modified_objects
0
 

Author Comment

by:mahjag
Comment Utility
Hi Sdstuber

I dont understand how the modified objects is being used - are you saying the user values can be got from FUNCTION get_my_date ? I am confused as to how to take your solution and implement the way that I have asked for?
0
 

Author Comment

by:mahjag
Comment Utility
Hi slightwv

Are you saying the someother table will get the user input start dates? -
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> I am confused as to how to take your solution and implement the way that I have asked for?

what you have asked for is not possible.  

What slightwv and I have both suggested are alternates.

My example view IS parameterized.  The results of the view are based on the function result.  The view results are based on the parameters given with the SET_MY_DATE procedure.  If that procedure isn't called, the view will always return no rows.

In order for the function to return a meaningful fliter for the view, you must first set a parameter.


Since you are using dates are your input,  you would use the exact same idea I have above.

In fact, to test, you could use the package above with no modifications.

Then, in your view,  where you currently have something like  

WHERE your_start_date >= TO_DATE('01-JAN-2012','DD-MON-YYYY')

you would change that to

WHERE your_start_date >= my_view_pkg.get_my_date


Now, when you query the view, the results will be based on whatever you pass into the SET_MY_DATE procedure
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Are you saying the someother table will get the user input start dates? -

someOtherTable will join on whatever columns you use to join to MyView.  The rows in MyView will be restricted to those dates.

Unless we have additional information, our responses will continue to be vague.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Grading with anything less than an A is inappropriate if you haven't responded to the posts given.

Please respond with adequate explanation of why you think a B is appropriate given that you failed to ask for any further information from either of the participants
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Why did you give a B grade?

If you neededed additional information you should have asked.
0
 

Expert Comment

by:gs79
Comment Utility
another way to do this is creating a view on the top of a parallel pipelined function some thing like below:

create or replace view my_vw as
select * from table(select * from pipe_fn(<parameters>))

The function does all the calcn and results a dataset which can be exposed via view..


however this method is also another alternative and is not exactly what you want to do..
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now