Re: Insert with parameterized table name?

Hi Brian,

You can use the DBMS_SQL package, which allows for a variable table name. I have used DBMS_SQL on the database side, and I believe that it will work on the Pro side of things.

With DBMS_SQL, you construct the entire SQL statement in a text field or like, and then execute it using DBMS_SQL.EXECUTE.

Rgds
Andrew

bconver_at_uswest.com wrote:

> Hi all,
>
> I'm using 7.3.4 and need to perform an insert into a table using a parameter
> for the table name. However, the catch is that I'm trying it through PRO-C.
> INSERT INTO :tablename (x, y, z) VALUES(:x, :y, :z);
>
> something like this anyway. The proc compiler doesn't like me using the
> variable reference for the table name. I'm looking for an alternative.
>
> does anyone know how to do something like this in PRO-C? (I could do it
> through ODBC, but that isn't an option). How about through a stored
> procedure or trigger? Is that possible, or will Oracle puke on a
> parameterized table name there as well...
>
> For anyone wondering why I want to parameterize the table name, I have a
> database with over 30 duplicate tables. Data of different types goes into
> different tables with the same data, but with specialized id type. Similar
> to a customer database with customer data split into tables by customer home
> state. Don't want to hard-code all possible table names, since tables can be
> added at anytime...and I'd like to avoid re-compilations every time we add a
> new table to the system.
>
> If you have a possible solution to this, please mail me at
> bconver_at_uswest.com
>
> thanks,
> Brian.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Apr 30 1999 - 20:14:30 CDT