Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Unanswered: Conditional / Dynamic SQL

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:

    Code:
    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;

Posting Permissions

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