Re: Dynamic Table Names in Dynamic SQL

robert.goodwin_at_msfc.nasa.gov (Robert Goodwin) 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?
 

>Thanks for any help. E-mail response if possible.

> :
> /^\
> : :
> :___:
>___.^:_._:^._______________________________________________________________
> :-:.^.:-: :
> :_:|=|:_: Robert Goodwin : phone: (205)461-4582
> : :|.|: : Database Development : fax: (205)461-4999
> :-/|.|\-: New Technology, Inc. :
> :/ |.| \: Huntsville, AL :

 (__|.|__)                        :


>   :_:(|):_:  NASA/MSFC             :  e-mail: robert.goodwin_at_msfc.nasa.gov


> --/^\^|^/^\---------------------------------------------------------------

>   /\\   //\

There is no way to pass the table name as a bind variable (and then use the OPEN...USING statement).

As for the performance, well, we sure can save some code writing, but I don't see any scope for improvement in the query performance.

Ananta Narayanan

The above views are my own and is offerred with no warranty of any kind. Other standard disclaimers apply.

• What we know is very little, What we don't know is immense. .. Leibnitz

  ---