Home » SQL & PL/SQL » SQL & PL/SQL » View with params from user
View with params from user [message #425650] Sun, 11 October 2009 04:15 Go to next message
eyalle
Messages: 5
Registered: October 2009
Junior Member
Hello,
i have this following code:

select T_NAME, T_ID, G_NAME
from T,G
where T.G_ID = G.G_ID
and T_ID in( select T_ID from T minus select T_ID from FT where F = changing_input);

the "changing input" is an input that is changed at run time.
my Q is how can i make a view so i can still use a changing variable ?
thank's
Eyal
Re: View with params from user [message #425654 is a reply to message #425650] Sun, 11 October 2009 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check "Dynamic Views, Creating Views based on Variables", an article written by Kevin Meade.
Re: View with params from user [message #425655 is a reply to message #425654] Sun, 11 October 2009 04:41 Go to previous messageGo to next message
eyalle
Messages: 5
Registered: October 2009
Junior Member
Hi,
i've read his article and tried to figure out what to do but i couldn't really match my code to his example.
can you help me with that pls ?
thank's
Re: View with params from user [message #425657 is a reply to message #425655] Sun, 11 October 2009 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a package variable.
SQL> create table t (id integer, val varchar2(1));

Table created.

SQL> insert into t 
  2  select level, chr(ascii('A')+level-1) from dual connect by level <= 10;

10 rows created.

SQL> select * from t;
        ID V
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E
         6 F
         7 G
         8 H
         9 I
        10 J

10 rows selected.

SQL> create or replace package p is
  2    myvalue integer;
  3    function get_input return integer;
  4  end;
  5  /

Package created.

SQL> create or replace package body p is
  2    function get_input return integer
  3    is 
  4    begin
  5      return myvalue;
  6    end;
  7  end;
  8  /

Package body created.

SQL> create or replace view v as select * from t where id=p.get_input;

View created.

SQL> exec p.myvalue := 5;

PL/SQL procedure successfully completed.

SQL> select * from v;
        ID V
---------- -
         5 E

1 row selected.

SQL> exec p.myvalue := 3;

PL/SQL procedure successfully completed.

SQL> select * from v;
        ID V
---------- -
         3 C

1 row selected.

Regards
Michel
Re: View with params from user [message #425658 is a reply to message #425657] Sun, 11 October 2009 05:24 Go to previous messageGo to next message
eyalle
Messages: 5
Registered: October 2009
Junior Member
Hi Michel,
thank's but it wont help me to do a static table
what i need is a dynamic view so im not managing the values..
thank's
Re: View with params from user [message #425660 is a reply to message #425658] Sun, 11 October 2009 05:30 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read CAREFULLY what I posted, espacially how the VIEW is defined.
You answer to me in 10 minutes, it is too fast to read, understand and test what I posted (without counting the time to receive the answer).

Regards
Michel

[Updated on: Sun, 11 October 2009 05:32]

Report message to a moderator

Previous Topic: Read Images from DB server
Next Topic: Displaying the Output from a variable in Stored procedure
Goto Forum:
  


Current Time: Mon Sep 26 05:55:08 CDT 2016

Total time taken to generate the page: 0.05385 seconds