Dynamic table creation not easy to do!!!

  We have written a database system that creates a new table for each data 'set' of data. We did this because each set of data consists of 1-n variable parameters, and 1-n measured values per variable parameter. For a given product, there may be 100-300 'sets' of data, or tests. Each test table is named with the convention T#xxxx_yy#zzzzz, where xxxx_yy are the product and revision number, and zzzzzz is the test 'name' (user defined).

  We setup a Form where the user can enter the list of measured values, and the list of variable parameters etc, then we go create the tables, and about 5 associated views. Unfortunately SqlForms cannot create tables, PL/SQL has not DDL commands - this is unfortunate, and I don't understand why not??? Any clues..

  We save all of the commands required to create the tables and view from SQLPLUS into a table. Each command line occupies a single row. We then HOST out to sqlplus, where the table is read and spooled to another file. This file is then executed with the _at_filename syntax from sqlplus. When the whole thing is done it returns to Sqlforms. I guess we could also have linked in some C code to Sqlforms....

  Oracle is not very good about 'variable' table types, Forms just doesn't support that. I really wish we had used a different design, because this one is a royal pain, but because of the data we have, it seemed warranted. What we really need is an OODB, but we have lots of Oracle installations and wanted to stay consistent. I don't know much (anything) about OODB, but I am looking forward to Oracle Corps. move into that arena....

   Ed Henderson
   tedh_at_tus.ssi1.com Received on Tue Nov 02 1993 - 18:50:21 CET