Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: pass parameter to a view??

    hello. can you define a view that takes in parameters?
    somewhat familiar with the &<variable> combo, but not sure how i'd implement creating a view that accepts input parameters
    something like ....

    CREATE OR REPLACE VIEW view_test (col1, col2, col3, var1 IN varchar2, var2 IN number) AS
    select col1, col2, col3
    from tableX
    where col5 = &var1
    and col6 = &var2


    is this even possible? if so an example would be appreciated! if not any suggestions would be appreciated also.
    thanks very much

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That is NOT how you use a view. A view is a stored query that acts like a table. If you have to do a restricted select from the view use a where clause on your select.

    CREATE OR REPLACE VIEW view_test as
    select col1, col2, col3,col5,col6
    from tableX;

    select col1,col2,col3
    from view_test
    where col5 = &var1
    and col6 = &var2;

    When you use a view, the conditions in your where clause are merged with the conditions in the stored query (view) and run against the base tables.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2005
    Location
    Chesterland, OH
    Posts
    9

    We created a little table to help us out

    We wanted to restrict our view to contain subsets of the data at certain points in time. We created a little table that had criteria in it that could be changed on occasion. The table only had 1 row on it. The view joined to the table and used the fields.

    Lets just say we called it Parm_Tbl. It had a few fields that served as controls to the view

    select col1, col2, col3
    from tableX, parm_tbl
    where col5 = Parm_tbl.var1
    and col6 = Parm_tbl.var2

    This may or may not help depending on how often the variables need to change.

  4. #4
    Join Date
    Nov 2005
    Posts
    4
    hello bill. thanks for the reply. i realize this is not the normal usage of a view and understand exactly what a view is. we have a situation at work where we'd like to be able to reference a view from an application (Kintana) in such a way where need to have parameters passed to its call.
    from my searches on the net, i haven't seen this, so i thought i'd try posting here. I had found something close, but not truly what we need...

    SQL> create view myview as select * from t where x = userenv('client_info')
    SQL> exec dbms_application_info.set_client_info('my_data')
    SQL> select * from myview;


    this is using environment variables. just looking to find out if this is possible at all with standard.
    my first assumption was that this is not possible, but i wanted to check with others to see if it was.
    we may end up defining a function and selecting that from dual. would be much easier with a dynamic view.
    please let me know your thoughts. thanks!


    leslie, thanks also for the reply, but that unfortunatley wouldn't fit with what i was trying to do. the seconds table holding the values would have to be dynamically populated in order for that to work. thanks though
    Last edited by msimon7; 11-16-05 at 16:49.

  5. #5
    Join Date
    Nov 2005
    Posts
    17
    DBMS_APPLICATION_INFO is a standard way of using "parameters". Another option is to create a function that returns a nested table as its return datatype. The function can except parameters. The SQL takes the form of:

    SELECT ....
    FROM TABLE(<function>(parm1,...,parmN)) x

  6. #6
    Join Date
    Nov 2005
    Posts
    4
    thanks cmerry, i was thinking that may be the way we have to end up going (a function call that returns a results set as its output). one issue we are facing is that where we are calling it from is basically only a SQL select area, meaning nothing more than a select is possible. need to look into how the app handles ref cursors or nested table output and can it traverse the results. thanks again for the reply.

  7. #7
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    If you're going to use a function, please you a PIPELINED function, otherwise you'll be in problems (depending on the amount of data).
    I'm using some pipelined functions as views with params, I think that's what you're looking.

  8. #8
    Join Date
    Nov 2005
    Posts
    4
    thank you DKG. i was not aware of that functionality. i will look further into it.

  9. #9
    Join Date
    Sep 2013
    Posts
    1
    Quote Originally Posted by DKG View Post
    If you're going to use a function, please you a PIPELINED function, otherwise you'll be in problems (depending on the amount of data).
    I'm using some pipelined functions as views with params, I think that's what you're looking.
    please can you give example code that shows how to create parameterized view via pipelined function. the closed I have got to achieving this is using SYS_REFCURSOR as following example:
    CREATE OR REPLACE FUNCTION param_vw(p1 as varchar2)
    RETURN SYS_REFCURSOR
    IS
    l_rc SYS_REFCURSOR;
    BEGIN
    OPEN l_rc
    FOR
    Select p1, '1jkhkhk','yuiyi' from dual
    union all
    Select p1, '2jkhkhk','yuiyi' from dual ;
    RETURN l_rc;
    END;

    above is crude use of paramater i know. but i basically want to use it as a view in a select like below. below wont work. is there something I need to wrap the function call in after the 'from' statement, to get the returned cursor to be used as a table.

    select * from param_vw('t1')
    Last edited by parmar123; 09-12-13 at 13:31.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Do you realize this thread is more than 4.5 YEARS old?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •