Re: Table substitution with PRO*C Dynamic SQL

In article <1v2fm0$ivu_at_skates.gsfc.nasa.gov> joefw_at_eosdev1.gsfc.nasa.gov writes:
> [...] while I accept that
> you can't do table name substitution with Dynamic Method 3, I am still
> confused by the following statment in the ORACLE Precompiler manual:
>
> "the names of database objects such as tables and columns need not be specified
> until runtime"
>
> This is located in the section on Using Method 3. Am I misinterpreting what
> it says (that with Method 3 you don't have to name the table until runtime),
> or, as it seems more likely now, is this a documentation mistake and it should
> just say "such as columns"?

No, the manual is correct. All this means is that a SQL statement in dynamic SQL (of any method) is or need not be completely formed until runtime. So you can "splice" table or column names into the statement before it is executed (method 1) or prepared (methods 2, 3, and 4).

One implication of this is that the precompile time option SQLCHECK=SEMANTICS is only really meaningful for static SQL statements. You can have

select dname from xxzzy where deptno = :number

in a method 3 dynamic SQL program, then correct it to

select dname from dept where deptno = :number

before it is parsed (with PREPARE, at runtime), and SQLCHECK will not complain.

You can't use a *host variable* to contain a table or a column name, but you can always fudge the names in the SQL statement at runtime.

Do follow the previous poster's suggestion and use Method 4 (with the SQLDA) for any but the simplest queries.

--Tim (tssmith_at_oracle) Received on Wed Jun 09 1993 - 05:30:51 CEST