Re: Insert with parameterized table name?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 01 May 1999 12:04:58 GMT
Message-ID: <372aed78.5101054@192.86.155.100>

A copy of this was sent to bconver_at_uswest.com (if that email address didn't require changing) On Fri, 30 Apr 1999 21:26:57 GMT, you 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...
>

You cannot bind an identifier to a statement like that. A statement with bind variables is like a compiled program. the security, access plans, etc are built for a parsed statement. if we don't know what objects you are accessing -- its impossible to build a plan or figure out if you have access to such an object.

You simply need to use a little dynamic sql. since the inputs to the query don't change, its quite easy. here is an example:

static void process( void )
{
EXEC SQL BEGIN DECLARE SECTION;
varchar sqlstmt[255];

int     x = 5;
int     y = 10;

EXEC SQL END DECLARE SECTION;     exec sql drop table t;

    exec sql whenever SQLERROR do sqlerror_hard();     exec sql create table t ( x int, y int );

    sprintf( sqlstmt.arr, "insert into %s values ( :1, :2 )", "t" );     sqlstmt.len = strlen( sqlstmt.arr );

    exec sql prepare s from :sqlstmt;
    exec sql execute s using :x, :y;

    exec sql commit work;
}

so, you'll prepare/execute USING

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 01 1999 - 07:04:58 CDT