Re: Creating tables via Pro*C using variable names

In article <1992May20.175419.17302_at_wdl.loral.com> mdm_at_wdl1.wdl.loral.com (Mike D Marchionna) writes:
> Help! I'm a Oracle neophyte and I've run into a roadblock. I can't seem
> to get my Pro*C program to use a variable name in place of a table name.

This is a common mistake, everyone makes it at least once.

Host variables in embedded SQL are used for data input and output. Internally, and transparently to the Pro*C programmer, the database kernel and the precompiler runtime conspire to bind the addresses of host variables, and when SQL statements are executed data is copied into and out of the host variable.

So, you can't use a host variable to stand for a SQL keyword or the name of a database object, such as a table or column name. T'ain't legal.

However, there is a very easy way to get the result you are trying for: use dynamic SQL. In dynamic SQL, you can use a host variable for a complete SQL statement. Here's a sketchy example:

EXEC SQL BEGIN DECLARE SECTION;
    char sql_stmt[80];
EXEC SQL END DECLARE SECTION;
    char t_name[20];

    ...
    printf("enter the new table name: ")     gets(t_name);

    strcpy(sql_stmt, "CREATE TABLE ");
    strcat(sql_stmt, t_name);
    strcat(sql_stmt, " (col1 CHAR(10), col2 NUMBER);");

Voila! Of course, you could also make column names, datatypes, column lengths, WHERE clause conditions, and so on settable at runtime with this technique.     

You can also use the PREPARE statement, what Oracle calls Dynamic SQL Method 2. Later on, when you become an ORACLE guru, you can explore the arcane mysteries of dynamic SQL with the SQLDA. This allows you to do just about anything you want, at the expense of a lot of complexity.

A note on documentation--

I don't know what Pro*C version you're running, but if you don't have Version 1.3 or later precompiler documentation, get it! (And an upgrade to the 1.3 software too.) The newer documentation is much improved. There are Pro*C example programs in Chapter 3 (or Chapter 4 in later versions) that demonstrate the technique I sketched out. The demo programs are also available on-line.

--Tim (tssmith_at_netcom.com) or (tssmith_at_oracle.com) Received on Thu May 21 1992 - 01:08:55 CEST