create a parametrized view

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.
mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mahjagAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mahjagAuthor Commented:
Hi slightwv

Are you saying the someother table will get the user input start dates? -
0
sdstuberCommented:
>>> 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
slightwv (䄆 Netminder) Commented:
>>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
sdstuberCommented:
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
slightwv (䄆 Netminder) Commented:
Why did you give a B grade?

If you neededed additional information you should have asked.
0
gs79Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.