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

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.

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,

Try this:

HTH,

clio_usa - OCP 8/8i/9i DBA