Solved

Parameterized View in Oracle

Posted on 2004-04-30
19
11,927 Views
Last Modified: 2011-08-18
Hi All,
I want to create a view which will accept parameters from user.
Ex:- View has 2 columns
1) ID
2) Total Payment Received During Report From date and Report Through Date
3) Total Emails received from the user during Report From date and Report Through Date

All 3 columns are from 3 different tables.

User should be able to select from this view by passing From Date and Through Date.

Is this possible?

Thanks in Advance,

0
Comment
Question by:Kajunki
  • 4
  • 4
  • 2
  • +4
19 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 10962889
No.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 10963088
Now a "no" answer is likely not what you were hoping for, but Oracle views do not accept parameters so the simple answer is no.

I understand that you want to solve the business problem though, so you are likely looking for a way to do that in Oracle.  A big complication here is the fact that you want matching data returned from two different tables for possibly short periods of time, when maybe one table or the other has no matching records.  You have to be careful with the solution design here to make sure you don't hide the data that is there (like a simple join view would do) if/when this happens.

Since Oracle PL\SQL functions can accept input parameters, one option would be to create a function for each table that accepts the date parameters and returns the total (or zero) for each table.  These functions could then be called from a single select statement, but not from a view (because of the limitation of not being able to pass parameters to a view).

Depending on your version of Oracle, another option would be to nest select statements something like this:
select m.id, (select sum(payment_amount) from payments p where p.from_date >= [from_date] and p.through_date <= [through_date] and p.id = m.id) "total payments",
(select count(*) from emails e where e.from_date >= [from_date] and e.through_date <= [through_date]
and e.id = m.id) "Total Emails"
from [main_table] m



0
 
LVL 3

Expert Comment

by:davidshockey
ID: 10963102
How are the three tables joined?
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10963138

Take a look at pipelined functions.  The function could assemble the data from the three tables.  Once you have setup the function, you could query with:

select *
from table(f_your_func(start,end));

0
 

Author Comment

by:Kajunki
ID: 10963721
Currently I have used pipelined function to implement.
Problem with pipelined function is
1) users cant describe it to see the columns,
2) They cannot use Group by

Please let me know if there is any way to achieve this with pipelined functions.
0
 

Author Comment

by:Kajunki
ID: 10963726
davidshockey- The tables are connected with ID Field.
0
 
LVL 3

Expert Comment

by:davidshockey
ID: 10963955
This is untested but I think its on the right track...

create view kajunki (id, date, payment, email) as
select id, date, payment, 0 from tbl2
union all
select id, date, 0, 1 from tbl3

then do this

select id, sum(payment), count(email)
from kajunki
where date between startdate and enddate
group by id
0
 
LVL 3

Accepted Solution

by:
davidshockey earned 168 total points
ID: 10964226
I'm about to leave the office and had some second thoughts... count(email) in the query should be sum(email).  

create view kajunki (id, tdate, payment, email) as
select id, pdate, payment, 0 from tbl2
union all
select id, edate, 0, 1 from tbl3

then do this

select id, sum(payment), sum(email)
from kajunki
where tdate between startdate and enddate
group by id
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 13

Assisted Solution

by:riazpk
riazpk earned 166 total points
ID: 10964925
Ok Here is a test case for that (using stored procedure...you can grant execute on that procedure to the users):

SQL> create table mytable(id number);

Table created.

SQL> ed
Wrote file afiedt.buf

  1* create table mytable1(amount number,id number, payment_date date )
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1* create table mytable2(id number, email_date date )
SQL> /

Table created.

SQL> insert into mytable select rownum from user_objects where rownum<=10;

10 rows created.


SQL> ed
Wrote file afiedt.buf

  1  insert into mytable1 select rownum*100,rownum,sysdate+rownum
  2* from user_objects where rownum<=10
SQL> /

10 rows created.

SQL> ed
Wrote file afiedt.buf

  1  insert into mytable2 select rownum,sysdate+rownum
  2* from user_objects where rownum<=10
SQL> /

10 rows created.

SQL> commit;

Commit complete.

SQL> select * From mytable2;

       ID EMAIL_DAT                                                                                
--------- ---------                                                                                
        1 02-MAY-04                                                                                
        2 03-MAY-04                                                                                
        3 04-MAY-04                                                                                
        4 05-MAY-04                                                                                
        5 06-MAY-04                                                                                
        6 07-MAY-04                                                                                
        7 08-MAY-04                                                                                
        8 09-MAY-04                                                                                
        9 10-MAY-04                                                                                
       10 11-MAY-04                                                                                

10 rows selected.

SQL> ed
Wrote file afiedt.buf

  1* select * From mytable1
SQL> /

   AMOUNT        ID PAYMENT_D                                                                      
--------- --------- ---------                                                                      
      100         1 02-MAY-04                                                                      
      200         2 03-MAY-04                                                                      
      300         3 04-MAY-04                                                                      
      400         4 05-MAY-04                                                                      
      500         5 06-MAY-04                                                                      
      600         6 07-MAY-04                                                                      
      700         7 08-MAY-04                                                                      
      800         8 09-MAY-04                                                                      
      900         9 10-MAY-04                                                                      
     1000        10 11-MAY-04                                                                      

10 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure myproc(from_date in date , to_date in date )
  2  is
  3  amt number;
  4  cnt1 number;
  5  cnt2 number;
  6  begin
  7  dbms_output.put_line('ID'||'             '||'Total Payment'||'             '||'Total Emails');
  8  for x in (Select id from mytable)
  9  loop
 10  select nvl(sum(amount),0) into amt from mytable1 where id=x.id and payment_date between from_date and to_date;
 11  select count(*) into cnt1 from mytable2 where id=x.id and email_date between from_date and to_date;
 12  dbms_output.put_line(x.id||'             '||lpad(amt,10,' ')||'             '||lpad(cnt1,10,' '));
 13  end loop;
 14* end;
SQL> /

Procedure created.

SQL> execute myproc(to_date('02-MAY-04'),to_date('11-MAY-04'));
ID             Total Payment             Total Emails                                              
1                    100                      1                                                    
2                    200                      1                                                    
3                    300                      1                                                    
4                    400                      1                                                    
5                    500                      1                                                    
6                    600                      1                                                    
7                    700                      1                                                    
8                    800                      1                                                    
9                    900                      1                                                    
10                      0                      0                                                    

PL/SQL procedure successfully completed.

SQL> spo off
0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 166 total points
ID: 10967607
Hi Kajunki, My answer is yes. It is possible :) Relief .. huh ;)

Well, you can have a view that can change dynamically. Have a look at my example below :

SQL> select * from tab_cust;

   CUST_ID CUST_NAME
---------- ----------
         1 cust1
         2 cust2
         3 cust3
         4 cust4
         5 cust5
         6 cust6
         7 cust7
         8 cust8
         9 cust9
        10 cust10

10 rows selected.

SQL> select * from tab_emails;

        ID     EMAILS EMAIL_DAT
---------- ---------- ---------
         1         10 01-MAY-04
         2          5 21-APR-04
         3          5 01-MAY-04
         4          8 01-MAY-04
         5         12 21-APR-04
         6          1 01-MAY-04
         7          2 01-MAY-04
         8          1 01-MAY-04
         9          3 29-APR-04
        10          1 29-APR-04

10 rows selected.

SQL> select * from tab_paym;

        ID    PAY_AMT PAY_DATE
---------- ---------- ---------
         1        100 21-APR-04
         1        250 22-APR-04
         2        350 21-APR-04
         3        350 26-APR-04
         4        100 29-APR-04
         4        200 30-APR-04
         4         50 01-MAY-04
         5        350 26-APR-04
         6        350 23-APR-04
         7        350 01-MAY-04
         8        350 01-MAY-04
         9        350 01-MAY-04
        10        200 29-APR-04

13 rows selected.

Now lets create a package, which we can use to store the report_from and report_to dates :

SQL>

create or replace package view_pack as
report_from  date;
report_to    date;
end view_pack;
/

Now lets create two functions, that will return the value of the two package variables that we have created :

SQL> create or replace function report_from return date
  2  is
  3  begin
  4  return view_pack.report_from;
  5  end;
  6  /

Function created.

SQL> create or replace function report_to return date
  2  is
  3  begin
  4  return view_pack.report_to;
  5  end;
  6  /

Function created.

Using the functions created above, lets now create a View on our three tables :

create view cust_view as
select a.cust_id id, sum(b.pay_amt) tot_pay, sum(c.emails) tot_emails
from tab_cust a, tab_paym b, tab_emails c
where a.cust_id = b.id and a.cust_id = c.id
and b.pay_date between report_from and report_to
and c.email_date between report_from and report_to
group by a.cust_id
/


Now lets populate the values in these package variables, assuming we need records between sysdate and sysdate-10 :

begin
view_pack.report_from := sysdate - 10;
view_pack.report_to := sysdate;
end;
/

Select the records from the view now :

SQL> select * from cust_view;

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         1        250         10
         3        350          5
         4        350         24
         6        350          1
         7        350          2
         8        350          1
         9        350          3
        10        200          1

8 rows selected.

Just to demonstrate this again, lets change the values of the report_from and report_to package variables, and see the effect on our query :


SQL> begin
  2  view_pack.report_from := sysdate - 5;
  3  view_pack.report_to := sysdate;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select * from cust_view;

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         4        350         24
         7        350          2
         8        350          1
         9        350          3
        10        200          1

Thats it, you are all done. You have your dynamic view Ready :)

Let me know if you need more help on this.
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10967985
I'm a little uncertain of what you mean by "accept parameters from user".  Can you describe some of the contraints that you have?


Here are some general options that come to mind:
-- user can run a sqlplus script that prompts for start and end dates... then runs an anonymous block to get results
-- user inserts start and end date into a global temp table... then join GT with your select.
-- use sys_context to set the start and end dates.. then your select that reference sys_context for the appropriate dates.


I can give specific example is needed, but it would be nice to know which type of option is most viable in your environment.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10969057
Though I have already provided a solution for dynamic view above, but I would like to share a solution using the PIPELINED table function also.

I was looking at your concerns related to PIPELINED table function :

-->1) users cant describe it to see the columns,
-->2) They cannot use Group by
-->Please let me know if there is any way to achieve this with pipelined functions.

1. You can describe the function, which will tell that there are two inputs required for this function. If you wish to see the return structure, you can describe the structure of table that is returned by the function. An example could be :

SQL> desc custview
FUNCTION custview RETURNS CUST_VIEW_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_DT                       DATE                    IN
 END_DT                         DATE                    IN

SQL> desc CUST_VIEW_TABLE
 cust_view1_table TABLE OF CUST_VIEW
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TOT_PAY                                            NUMBER
 TOT_EMAILS                                         NUMBER

2. You can have the GROUP BY clause within the function code. I am not sure why you are facing the problem with it.

3. If you are interested in looking at the option of PIPELINED table function, then have a look at my solution below :

create or replace type cust_view
  as object (ID NUMBER, TOT_PAY NUMBER, TOT_EMAILS number); --structure of object, that you are interested in

create type cust_view_table
  as table of cust_view;

create or replace function custview (start_dt date, end_dt date)
  return cust_view_table pipelined
as
sdate  date;
edate  date;

cursor c1 is
select a.cust_id id, sum(b.pay_amt) tot_pay, sum(c.emails) tot_emails
from tab_cust a, tab_paym b, tab_emails c
where a.cust_id = b.id and a.cust_id = c.id
and b.pay_date between sdate and edate
and c.email_date between sdate and edate
group by a.cust_id;

begin
    sdate := start_dt;
    edate := end_dt;
    for c1_row in c1
    loop
        PIPE ROW(cust_view(c1_row.id, c1_row.tot_pay, c1_row.tot_emails)) ;
    end loop;
return;
end custview;
/

-------
You may note, that you can have the group by clause within the cursor definition that will be used to find out the relevant rows from the three tables.

Now, that we have created the function, we can use the same :

SQL> select * from table (custview1(to_date('21-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         1        350         20
         2        350          5
         3        350          5
         4        350         24
         5        350         12
         6        350          1
         7        350          2
         8        350          1
         9        350          3
        10        200          1

SQL> select * from table (custview1(to_date('28-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         4        350         24
         7        350          2
         8        350          1
         9        350          3
        10        200          1

Hope this helps. Do let me know, if you need any more info on this.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10969065
well, the name of function is custview and not custview1. I made a typo :) when u select the records, u would do :

SQL> select * from table (custview(to_date('21-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         1        350         20
         2        350          5
         3        350          5
         4        350         24
         5        350         12
         6        350          1
         7        350          2
         8        350          1
         9        350          3
        10        200          1

SQL> select * from table (custview(to_date('28-APR-04'), to_date('02-MAY-04')));

        ID    TOT_PAY TOT_EMAILS
---------- ---------- ----------
         4        350         24
         7        350          2
         8        350          1
         9        350          3
        10        200          1

Hope this helps. Do let me know, if you need any more info on this.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12345304
@@TheLearnedOne : I do not agree to the recommendations.

davidshockey {http:#10964226} solution will not work, mainly because you cannot have the input parameters defined in a view as suggested. In that way, only the column names (for the view) can be defined. Have a look at the following example :

SQL> create or replace view vsysdate (mydummy) as
  2  select sysdate from dual where dummy = mydummy;
select sysdate from dual where dummy = mydummy
                                       *
ERROR at line 2:
ORA-00904: "MYDUMMY": invalid identifier

SQL> create or replace view vsysdate (mydummy) as
  2  select sysdate from dual where dummy = 'X';

View created.

SQL> select * from vsysdate;

MYDUMMY
---------
19-OCT-04


riazpk {http:#10964925} solution is a stored procedure, and you will not be able to select the values from a stored procedure and put it into a variable or record, as you can do with a normal view.

However, the PIPELINED table function will allow you to use it as a view, and you could do a select INTO using the table function as I have suggested.

I am sorry, but I would not like to share the points with riazpk and davidshockey, as they have not provided the correct solution.
0
 
LVL 3

Expert Comment

by:davidshockey
ID: 12372959
I disagree with pratikroy's assessment.

Because kajunki called this a parameterized view in the title, pratrikroy is hung on the idea that parameters must be passed to the view.  This is not necessary to the solution.  His characterization of my solution with the mydummy query is spurious, having little relation to my proposal.

Furthermore, kajunki stated that it was a requirement that the users be able to use group by in their query.  Pratikroy waves this requirement off by saying that the group by can be part of the cursor select statement in the function - NOT the same thing.

My solution works (I tested it), and is far simpler than the pipelined table function solution.

I am perfectly OK with sharing the points with those who contributed.
0
 

Expert Comment

by:Lionel031700
ID: 20217195
I found this while doing a search. Perhaps it can help you
------------
Create context params using setmm;
------------
create package setmm is
procedure minmax(vmin in number, vmax in number);
end setmm;
/
------------
create package body setmm is
procedure minmax(vmin in number, vmax in number) is
begin
dbms_session.set_context(
namespace => 'params', attribute => 'minval', value => vmin);
dbms_session.set_context(
namespace => 'params', attribute => 'maxval', value => vmax);
end minmax;
end setmm;
/
------------
create view parametrized as
   select * from (select level n from dual connect by level <= 1000)
   where n between to_number(sys_context('params', 'minval'))
and to_number(sys_context('params', 'maxval'));
------------
SQL> exec setmm.minmax(vmin => 5, vmax => 14)

SQL> select * from parametrized;
5
6
7
8
9
10
11
12
13
14

10

SQL> exec setmm.minmax(vmin => 2, vmax => 4)


SQL> select * from parametrized;
2
3
4

Thanks and Regards,
Lionel
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

13 Experts available now in Live!

Get 1:1 Help Now