Creating Temporary Tables with Dynamic Names

Hello Beloved Oracle Gurus (once again!):

This time I am trying to create temporary tables in Pro*C. My processes create tables, put some data in them, create reports from them, then drop the tables. All this works fine if the table name is static (i.e., if the temporary table is always called the same thing and the EXEC SQL statements are hardcoded).

However, in some of my application's processes, the table name changes; for example, it could be based on the current customer's name, as in tbl_mycorp, tbl_yourcorp. Naturally, this table name is determined at run time, depending on which customer is being processed. I use dynamic SQL to accomplish this.

I can create the table and insert into it with no problems; however, I cannot drop the table. I get error -56 ("DDL lock on object '%s' is already held in an incompatible mode") or error -58 ("timeout occurred while waiting for DDL lock"). I noticed that I have a Shared lock on the table. I also get this Shared lock when the table name is static, but I am able to drop the staticnamed  table. What difference should it make whether my statements are regular Embedded SQL or Dynamic SQL?

I am running RDBMS V6.0.36.0.1 and Pro*C V1.4.8.2.2.

Thanks,
Marianne Received on Mon Feb 22 1993 - 20:23:45 CET