Solved

create a parametrized view

Posted on 2012-03-19
10
292 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
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 73

Accepted Solution

by:
sdstuber earned 123 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
 

Author Comment

by:mahjag
ID: 37738247
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
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 76

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 73

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

911 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

20 Experts available now in Live!

Get 1:1 Help Now