[tgrebe]

Hello

I am trying to figure out if it's possible to use conditional sql in Oracle 9i to write an SQL statement such as the following;

select a.itema, b.itemb, c.itemc
from tablea a, tableb b, tablec c
where a.index=b.index and a.index=c.index

This needs to be one global, dynamic or conditional SQL query that runs across multiple users in the instance. The users may have some different attributes for these items and tables. For instance, for some of the users, itemb in tableb is called itemb1, other users do not have tablec at all. I've tried fooling around with the CASE statment a little, but no success so far. Any help would be appreciated.

[andrewst]

Wow, what a mess your database must be in!

You can interrogate ALL_TABLES and ALL_TAB_COLUMNS to find out the names of tables owned by any user, and then construct dynamic SQL like this:

declare
   l_sql long;
   c sys_refcursor;
   ...
begin
   l_sql := 'select ' || l_col1 || ' from ' || l_table_name1 || ' where '...;
   open c for l_sql;
   loop
      fetch c into ...;
      exit when c%notfound;
      ...
   end loop;
end;