Solved

create a parametrized view

Posted on 2012-03-19
10
293 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
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.

 

Author Comment

by:mahjag
ID: 37738247
Hi slightwv

Are you saying the someother table will get the user input start dates? -
0
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 92
oracle 11g 23 73
Include a logo in email body using Oracle utl_mail 11 30
ORA-02288: invalid OPEN mode 2 18
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

813 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

18 Experts available now in Live!

Get 1:1 Help Now