Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    1

    Unanswered: parameterized view

    hi,

    I'm trying to create a view that requires an input of variable instance on invocation, but am not succeeding. Can anyone give me a hand?? Maybe illustrate with an example... I'm pretty new to oracle and PL/SQL.

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It's not clear what you mean. A view is simply a stored query definition, it does not have parameters like a function or procedure.

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Quote Originally Posted by zeroone
    hi,

    I'm trying to create a view that requires an input of variable instance on invocation, but am not succeeding. Can anyone give me a hand?? Maybe illustrate with an example... I'm pretty new to oracle and PL/SQL.

    thanks

    1. Create or replace view testview
    as
    select table_name, column_name
    from user_tab_columns
    where table_name = userenv('client_info');

    2. exec dbms_application_info.set_client_info(<TableName>) ;

    3. Select * from testview;

    Step 2 sets the variable 'client_info' with the name of the table. And executing step 3 will select for that table. Is this what you require? If so, you can get more information at:

    http://asktom.oracle.com/pls/ask/f?p...:1448404423206,

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Quote Originally Posted by zeroone
    hi,

    I'm trying to create a view that requires an input of variable instance on invocation, but am not succeeding. Can anyone give me a hand?? Maybe illustrate with an example... I'm pretty new to oracle and PL/SQL.

    thanks

    Try this:

    Code:
    create package with global varaibles (or functions that
    returns this globals) for example
    
    create package x
    is
      g_1 number;
      g_2 number;
    end;
    /
    
    use global variables in view definition
    
    CREATE VIEW emp_com_12 AS
    SELECT * FROM EMP
    WHERE COM >= X.G_1
    AND COM <= X.G_2
    /
    
    set globals and refresh view data
    
    begin
      x.g_1 := 5;
      x.g_2 := 25;
    end;
    /
    
    select * from emp_com_12
    /
    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

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