Re: Dynamic SQL, Creating Table and insert into table

In article <CA40st.GwF_at_cbfsb.cb.att.com> gerette_at_cbnewsb.cb.att.com (marianne.g.pittorino) writes:
>
>>In article <C9vIB1.AKv_at_ms.uky.edu> rama_at_ms.uky.edu (Sivakumar Ramamoorthy) writes:
 

>>> i.e. I need something like
>>> EXEC SQL CREATE TABLE:X AS SELECT * FROM ANOTHERTABLE;
>>> Which of the four modes of dynamic SQL
>>> namely
>>> 1. EXECUTE IMMEDIATE
>>> 2. PREPARE AND EXECUTE
>>> 3. PREPARE AND FETCH
>>> 4. PREPARE, DESCRIBE AND FETCH
>>>
>>> should I use.
 

>>Any of them. The solution is to construct the sql string using C runtime
>>functions, such as strcpy() and strcat(). You cannot use a bind variable
>>for a table or column name in any SQL.
>>--
>>Dave Mausner / Sr Consultant / Datalogics division of Frame Technology Inc
>>441 W Huron / Chicago IL 60610 / +1-312-266-4450 / "Just show me the code"
>
>
>Be careful with this! Our application also creates dynamic tables (with the
>name determined at run time), inserts some data, then drops the table when
>finished. We have found that when you use the PREPARE/EXECUTE construct,
>the table gets created at the PREPARE statement, not the EXECUTE statement!
>It appears that DDL statements (e.g., create table, drop table) take effect
>after the PREPARE, but DML statements (e.g., insert, update, delete) take
>effect after the EXECUTE. Since this is a problem for our application,
>we have changed the PREPARE/EXECUTEs to EXECUTE IMMEDIATEs.
>
>Has anyone else seen this? Is this a feature or a bug? We are using RDBMS
>V6.0.36.7.1 and Pro*C 1.4.11.2.1.

This is a standard, if not completely-documented, feature. When you parse a DDL statement (which is what PREPARE does), it is also then executed. A reason for this is that there can be no bind variables in a DDL statement (you can't have host variables for table, column, or other DB object names), so there is no need to bind and then execute. So it's a feature in that it potentially reduces the number of network round-trips.

In the OCIs, doing an execute after a DDL parse is just a no-op, so I'm not sure what your precompiler problem is. You should be able to do

PREPARE "#1 ddl statement" (it's parsed and executed) EXECUTE "#1 ddl statement" (no effect)

as well as

PREPARE "#2 dml statement" (it's parsed) EXECUTE "#2 dml statement" (its host variables are bound, and it's executed)

In any event, we should document the behavior of PREPARE for DDL statements. I'll file a bug on it, and it will be documented in future precompiler manuals.

--Tim (tssmith_at_oracle.com) (Languages documentation) Received on Wed Jul 14 1993 - 00:01:57 CEST