Re: help: pro*c & creating tables

In article <1995May25.172150.16770_at_zippy.dct.ac.uk>, sa103_at_york.ac.uk says...
>
>hi,
>i'm using pro*c and oracle v6 on a vax.
>I'm trying to create a new table with a name that the user has entered on
 the
>command line. I copy this string into a varchar variable that is declared
 in
>the SQL declare section and then try to use this in a create table
 statement:
>
>EXEC SQL BEGIN DECLARE SECTION;
>VARCHAR tab_name[30];
>EXEC SQL END DECLARE SECTION;
>
>in main I copy the argument into a string :
>strcpy(tab_name.arr,argv[3]);
>
>and then create a table with the variable :
>EXEC SQL CREATE TABLE :tab_name
> ( etc etc );
>
>
>Pro*c comes up with an error that bind variables cannot be used in a
>create statement. Does anyone know how to get round this ?
>
>Another question :) I'm trying to do the same thing for column
>names and although this doesnt give me an error when compiling, the code
>does not execute.
>
>Thanks for any help
>
>--
>Seb Adams

It's possible to use Pro*C host variables in many, but not in all situations.

The following example is correct:

  EXEC SQL INSERT INTO tbl_blabla (col1, col2)

                        VALUES    (:hvar1, :hvar2);

But, I'm sure your Pro*C won't execute the following (like your CREATE TABLE)

  EXEC SQL INSERT INTO tbl_blabla (:hvar1, :hvar2)

                         VALUES    ('klappt', 'nicht');

You can only subsitute SQL "expressions" by host variables.

To use variable column names or table names or ... in your Pro*C SQL, you should use DYNAMIC SQL.

Stefan Received on Fri May 26 1995 - 00:00:00 CEST