How Can I write a PL/SQL routine that will generate a "CREATE TABLE" that will populate the table name from a value in existing table?

Besides the facts that both Oracle and SQL Server store data in rows and columns, and they both support the SQL language, there are many more differences between them than similarities.  A number of questions have been asked (and answered) regarding some of those differences on this site over the years.  You may find it helpful to search for some of them and read them.

One thing you should know is that Oracle PL\SQL is *NOT* optimized for DDL statements, like: create, drop, alter, etc.  PL\SQL is optimized for the four basic SQL verbs only: select, insert, update and delete.  Yes, it is possible to do DDL commands in PL\SQL but only if you use the "execute immediate" syntax or the more-complex procedures in the DBMS_SQL package.

Are you sure that creating tables dynamically in a stored procedure is the best way to solve the business problem you have?  That is a very non-typical approach in an Oracle system.

Hello,

I TOTALLY agree with you :), I am doing this for someone who does not have a clue of what is invovled who suggested that I use SQL statements.  I intially attempted this with the "execute immedate" approach below is the code that I was having issues with intially.

This is the code that I was working on and kept getting errors with the "EXECUTE IMMEDIATE" approach
I guess the MAIN thing I need to know is CAN I use this approach to extract the new TABLE NAMES from the EXISITING tables and also the new column names and attributes also.

DECLARE
X = OBJ_TYPE.OBJ_TYPE_NAMECREATE TABLE X (
NAME (VARCHAR2(100),
DESCRIPTION(VARCHAR2(100)
WHERE OBJ_TYPE.OBJ_TYPE_ID = 1;

EXAMPLE,  I need the code to extract from EXISITNG TABLE "OBJ_TYPE" the value in the column "OBJ_TYPE_NM", which is  "DESIGN_SPECIFICATION" - WHICH IS EQUAL TO X in my sample code. This will be the NEW TABLE NAME and the WHERE clause would EXTRACT the value in column OBJ_TYPE_ID which is this case would be WHERE OBJ_TYPE_ID = 1 and this would extract all REVELANT data to populate all the new table structures based on the where clause on each row EXAMPLE THE WHERE CLAUSE FOR OBJ_TYPE_ID IN WOULD EXTRACT THE FOLLOWING DATA
OBJ_TYPE_ID =2 NEW Table Name would be ATTRIBUTE
then the code would search in the next table OBJ_TYPE_RLTNSHP and the column Data_Type for the attribute table would be FK which is = to 2. and the code should continue until the entire table structures are complete as I listed in the above example.

I just need a start and I can complete this, I just need the proper syntax and the "EXECUTE IMMEDIATE" function to use with a WHERE clause to and the capability to automatically extract the values from the exisitng table to create new tables.

I know this is complex, and I really appreciate all efforts.
Thanks again.

 ...I know the code is wrong but hopefully you can work with the syntax...:)
The goal is to create a routine that would automatically populate data into these new table structures and the tables are to be created dynamically through code based on parameter values in existing tables.

AGAIN, the end result should generate a CREATE NEW TABLE statement in SQL plus and at some point have code to insert data into these new tables structures..

table as the sample below....

Create Table Data_Specification
Name            Varchar(200)            not null,
Description      varchar2(400)            null,
Data Type      String(20)            not null,
FK            String(1)            not null,
PK            String(1)            not null,
Required      string(1)            not null
);

Whoever is paying for this should be informed that what they are asking for is contrary to how Oracle works best.  Yes, it is possible in Oracle PL\SQL to use the "execute immediate" syntax to create new tables dynamically.  But whenever an application tries to force Oracle to do what it doesn't do very well (instead of taking advantage of the things that Oracle does do well) the coding gets complex, performance tends to be poor, and the ability to scale to handle multiple users is limited.

Please post the records from your existing OBJ_TYPE and OBJ_TYPE_RLTNSHP tables. That wud help us to code it a lot easier.

Hi Kamdar,

Below are the three tables that I am working with, I know this data is not "typical" data, in fact this requirement is not typical :), I hope to never have to do something like this again.

Ok, the first table OBJ_TYPE below is the table where we should extract the NEW Table names from, example the newly created table with the CREATE table function would create a table name <Design_Specification>, the second Table would be called <Attribute> and third table would be called <Entity>. and the code should be focused on parameters values of the OBJ_TYPE_ID  OR PRT_ID in each table where the value = 1, 2 or 3 and so forth.

Next the create the table statement would extract the values from each table starting with the OBJ_TYPE_RLTNSHP, and the first column to be generated from this table would be  <Datatype> and I guess and some point we would have to convert string to varchar2(100) to be acceptable in Oracle in order to generate a REAL table structure, don't worry about the null conditions, I can add that in later.

Table Name: Obj_Type

Column1            Column2
Obj_type_ID                           Obj_type_nm
1            Design_Specification
2            Attribute
3            Entity


Table Name:OBJ_TYPE_RLTNSHP
Column1            Column2            Column3
OBJ_TYPE_ID      RLTN_NM                           Obj_type_mn
1            Datatype            String
2            PK            String
3            REQUIRED                           Number

Table Name: Prprt
Column1:                         Column2            Column3
prt_id            prt_nm            prt_size
1            erpno            100
2            release            200
3            version            50

ALSO REMEMBER we are adding the two columns NAME and DESCRIPTION to each new generated table structure and remember THESE ARE TABLE STRUCTURES ONLY WITH NO DATA. I guess as part of the CREATE TABLE routine NAME and DESCRPTION are to manually hard coded with such as NAME (VARCHAR2(100) and DESCRIPTION VARCHAR2(100) something like that....I hope this helps :)

I guess the first table would look something like this....

Create Table Data_Specification

Name            varchar(200)      not null,
Description                            varchar2(400)      null,
Data Type                            String(20)            not null,
PK            String(1)            not null,
Required                            Number            not null
);
 

Thx for the clarification, it certainly helped :) Regarding the nullability of a column, going by ur earlier examples, i would assume that all the columns that are being created by extracing values from a table are NOT NULL. Correct me if this assumption is incorrect.

I believe the object_type_id values provided by u in the OBJ_TYPE_RLTNSHP are incorrect. If "datatype", "pk" and "required" are fields of the same design_specification table, then all these 3 rows should have object_type_id = 1 in the OBJ_TYPE_RLTNSHP table.

I also fail to understand the significance of the PARTS table. Please elaborate.

With the above observations & assumptions, below is the code to implement ur requirement. Note that the below procedure converts all the spaces within ur table-names or column-names to underscores(_) and "STRING" to VARCHAR2(100) to avoid errors. Also since its not tested, it may hv syntactical errors, but logic-wise, it shud be ok.

CREATE OR REPLACE PROCEDURE create_table IS
 
strtbl VARCHAR2(100) := 'CREATE TABLE ';
strcol VARCHAR2(100) := '(name VARCHAR2(200) NOT NULL, description VARCHAR2(400) NULL';
strsql VARCHAR2(4000);
 
BEGIN
 
FOR tbl in (SELECT * FROM obj_type) LOOP
    strsql := strtbl || REPLACE(tbl.obj_type_nm,' ','_') || strcol;
 
    FOR col in (SELECT * FROM obj_type_rltnshp WHERE obj_type_id = tbl.obj_type_id) LOOP
        strsql := strsql || ', ' || REPLACE(col.rltn_nm,' ','_') || ' ' ;
        strsql := strsql || DECODE(UPPER(col.obj_type_nm),'STRING','VARCHAR2(100)',UPPER(col.obj_type_nm)) || ' NOT NULL';  
    END LOOP;
	
    strsql := strsql || ')';
 
    EXECUTE IMMEDIATE (strsql);
END LOOP;
 
END create_table;

Select allOpen in new window

Thanks!! I will test this code when I get to work in the morning and let you know how it works :)

Hello,

I am getting complilation errors, could this be because the VALUES in the OBJ_TYPE table have spaces? example the value <Design Specification> is not listed in the Oracle schema as <Design_Specification> since these are values in rows that we are trying to convert to REAL TABLE NAMES. Real table names in Oracle cannot have spaces between them...SO...could this present a problem?

Thanks.

Yes, Oracle table and column names may not include spaces, but the "replace" command should have put underscores instead of spaces in the table and column names.

I'm still curious why someone thinks this approach is a good idea in Oracle.  Is this an attempt to have a "database independent" application that can run on multiple databases?

Thanks MarkGeer for your comment,

I just had a meeting with the "Lead Architecht" and suggested this WHOLE requirement is NOT feasible due to the actual string data. His orginal intent was to have some type of routine where no manual manipulation was involved and have CODE "do all the work", the the problem I am seeing is that when they get new spreadsheets in that will uploaded into Oracle table structures, the string names will have spaces and who knows what else...so I am not sure if it is feasible to write code with data that WE WILL NOT KNOW the format which may include many spaces between characters and other unknowns...do you agree?

Yes, this definitely looks to me like *NOT* a good plan for Oracle.  Oracle does many things well, but creating tables dynamically based on variable user input is *NOT* one of them!

I think its time to go back to the "Lead Architect" and determine what exactly is the business need, and how can the strengths of Oracle be used to meet those needs.  Or, maybe Oracle isn't the best tool for the job, but for us without knowing what the real business need is here, we don't know.

Hi Markgeer,

Me again....another question...if I got an error "Procedure Created with Compliation Errors", how can I DEBUG this in PL/SQL..I tried to DESC table Design_Specification TO SEE IF THIS CODE CREATED A NEW TABLE, but it does not exist.....so does this mean there is still something else wrong with the code?
Thanks again for all your valuable help on this convoluted requirement :).