Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem creating View with Parameters

782649Jul 1 2010 — edited Jul 2 2010
Hello,

i'm new working with PL/SQL and i'm trying to create a view that accepts parameters. I have two tables that i merge with UNION first, I would like to query a view that do that with parameters. It looks like that:

TABLE 1
col1,
col2,
col3

TABLE2
col1,
col2,
col3,
col4,
col5

View:
CREATE VIEW ALL_ADM_VIEW AS SELECT COL1, COL2, COL3 FROM
(SELECT COL1, COL2, COL3 FROM TABLE1
UNION COL1, COL2, COL3 FROM TABLE2
WHERE CURRENT_DATE BETWEEN TABLE2.COL4 AND TABLE2.COL5) U
WHERE U.COL3='PARAMETER';

I need to put the parameter value 'PARAMETER' for the View. Any Help?

Thanx in advance
This post has been answered by SomeoneElse on Jul 1 2010
Jump to Answer

Comments

Tubby
Are you sure you really want to create a view for this? Perhaps a stored procedure that takes in parameters and return a REF CURSOR would be more appropriate?

If you REALLY need to, here's a method you can use.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:906341500346611919

But i would tend to push you in the direction of a stored procedure to complete this task as opposed to the view.
William Robertson
Can you give an example of how you want to pass the parameter at runtime?
SomeoneElse
Answer
Create your view like this: 
CREATE VIEW ALL_ADM_VIEW AS 
SELECT COL1
      ,COL2
      ,COL3 
FROM   TABLE1
UNION  
SELECT COL1
      ,COL2
      ,COL3 
FROM   TABLE2
WHERE  CURRENT_DATE BETWEEN COL4 AND COL5
;
When you use the view in your code, that's where you use your variable: 
select ...
from   ALL_ADM_VIEW
where  COL3 = 'PARAMETER';
Marked as Answer by 782649 · Sep 27 2020
SomeoneElse
And if you create it that way, look what happens: 
SQL> create table table1
  2  (col1, col2, col3) as
  3  select object_name
  4        ,status
  5        ,object_type
  6  from   dba_objects;

Table created.

SQL> create table table2
  2  (col1, col2, col3, col4, col5) as
  3  select object_name
  4        ,status
  5        ,object_type
  6        ,created
  7        ,last_ddl_time
  8  from   dba_objects;

Table created.

SQL> CREATE VIEW ALL_ADM_VIEW AS
  2  SELECT COL1
  3        ,COL2
  4        ,COL3
  5  FROM   TABLE1
  6  UNION
  7  SELECT COL1
  8        ,COL2
  9        ,COL3
 10  FROM   TABLE2
 11  WHERE  sysdate BETWEEN COL4 AND COL5
 12  ;

View created.

SQL> explain plan for
  2  select *
  3  from   ALL_ADM_VIEW
  4  where  COL3 = 'PARAMETER';

Explained.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 2665559353

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  1648 |   131K|   300   (2)| 00:00:04 |
|   1 |  VIEW                | ALL_ADM_VIEW |  1648 |   131K|   300   (2)| 00:00:04 |
|   2 |   SORT UNIQUE        |              |  1648 | 64288 |   300  (58)| 00:00:04 |
|   3 |    UNION-ALL         |              |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| TABLE1       |  1647 | 64233 |   127   (1)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL| TABLE2       |     1 |    55 |   171   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("COL3"='PARAMETER')
   5 - filter("COL3"='PARAMETER' AND "COL5">=SYSDATE@! AND "COL4"<=SYSDATE@!)

18 rows selected.
Oracle is smart enough to push the predicate (your parameter) into the view so it can filter out those rows before it does the UNION (and the sort).
Billy Verreynne
user1136375 wrote:

i'm trying to create a view that accepts parameters.
SQL is not a procedural language. You do not create views (and other objects) as if they are procedures and then attempt calling these like objects with parameters.

You first need to understand the nature of the SQL language and how to use it - before looking at work-arounds like actually parameterising views using context variables. This is and always will be an exception to the rule and not how one should use SQL - and one needs to understand the SQL language before making use of such work-arounds.
782649
You are great! Thank you very much!
782649
i can understand you. Any way, i'm not purist. Bye!
Billy Verreynne
It is not about being a purist. It is all about understanding how to apply the tool (in this case the SQL language) that you are using.

Fortunately for you, you're not a carpenter as you would have been without a finger or three with the attitude of not needing to know how the tools you are using need to be used.
782649
OK. I'm not really an PL/SQL expert and it is not my ambition to become one. I had this problem and i solved that way. Thank you any way for your feedback. Bye.
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 30 2010
Added on Jul 1 2010
9 comments
1,277 views