Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

create a parametrized view

Posted on 2012-03-19
10
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 488 total points
ID: 37737907
>>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 74

Accepted Solution

by:
sdstuber earned 492 total points
ID: 37738013
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
ID: 37738235
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mahjag
ID: 37738247
Hi slightwv

Are you saying the someother table will get the user input start dates? -
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37738328
>>> 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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738334
>>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 74

Expert Comment

by:sdstuber
ID: 37738607
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738611
Why did you give a B grade?

If you neededed additional information you should have asked.
0
 

Expert Comment

by:gs79
ID: 37738632
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 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