Re: Dynamic Table Names in Dynamic SQL

Robert Goodwin (robert.goodwin_at_msfc.nasa.gov) wrote:
> I wonder if anyone else has had this problem...
> We're developing Pro*C code in which the table names in our SQL statements are
> dynamic...that is, the table names are different in different situations. We
> can accomplish this by simply building the entire SQL statement using the
> capabilities of C, and then executing the SQL statement. But, we'd like to be
> able to have a variable (a bind variable?) for the table name, and simply use
> that variable in the SQL statement. I would assume that this would be a
> little more efficient and straightforward than building the entire SQL
> statement each time. However, the Pro*C precompiler doesn't seem to like
> this. We can use bind variables for values of fields, but not for the table
> names. Does anyone know if there is a way to do what we're wanting to do?

I don't think so. dynamic sql is the solution; or if every user of the application logs in using their own oracle user account and one user uses only one oracle user account at a time; you can do something like:

say the table name is in variable table_name; 1. make dynamic sql statement to

    "create synonym temp_syn_for_table FOR user_name."||table_name.     execute that
2. In your sql statements refer to temp_syn_for_table.

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************