• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1074
  • Last Modified:

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?

I am sort of a newbie writing code in SQL*PLUS and PL/SQL, but I do have extensive programming exerience with VB, SQL Server, MS Access.

I have been given a task to programmatically dynamically create tables through using SQL statements OR some type of oracle procedure that will look at existing tables and extract values from the main table to give the NEW TABLE NAMES and table structure through code. I think once I get this started I can complete this task on my own hopefully :).

---The only columns and datatypes are to be hardcoded that will be inserted into each new table structure are below:
This is example of a SQL statement to hard code the columns NAME AND DESCRITION.
SQLstatement = "create table" + obj_type_nm 	
SQLstatement = SQLstatement " "\n" + "("
SQLstatement = SQLstatement " "\n" + "name		varchar2(100)	not null,"
SQLstatement = SQLstatement " "\n" + "description	varchar2(400)	null"
The CODE should work like this:
Loop through each existing table and first find the NEW TABLE NAME from the OBJ_TYPE Table which would be 
Then the code should find the NEXT TABLE "OBJ_TYPE_RLTNSHP" and extract the COLUMN value from the column named
OBJ_TYPE_NM based on the OBJ_TYPE_ID  = 1 WHICH WOULD BE in this case <DATA TYPE>
Then the code should go to the last table based on the same parameters in the next table "PRPRT" and 
extract the column value from the column name "prt_nm" where OBJ_TYPE_ID =1 which would be in this case <ERP_NO>
and so forth, this is just and EXAMPLE 
***PLEASE FEEL FREE TO CHANGE THE SQL statements, I was just trying to figure out a way to hard code the name and description columns since they should generate in every table.
These are the EXISTING TABLES which we will be extracting the NEW table NAME and columns, datatypes and attributes.
Table Name: Obj_Type
Coulumn1	Column2
Obj_type_ID	Obj_type_nm
1		Design_Specification
2		Attribute
3		Entity
Column1		Column2		Column3
OBJ_TYPE_ID	Data Type	Obj_type_mndtry_in
1		FK		String
2		PK		String
3		REQUIRED	Number
Table Name: Prprt
Column1:	Column2		Column3
prt_id		prt_nm		prt_size
1		erp_no		100
2		release		200
3		version		50
--Results of the Create Table STATEMENTS would return a table structure similar 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
Create Table Attribute
Description	varchar2(400)		null,
Data Type	String(20)		not null,
ERP_NO		String(100)		not null,
Release		String(200)		not null,
Version		String(50)		not null,

Open in new window

  • 6
  • 5
  • 3
2 Solutions
Mark GeerlingsDatabase AdministratorCommented:
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.
nbuchAuthor Commented:

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.


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
Mark GeerlingsDatabase AdministratorCommented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jinesh KamdarCommented:
Please post the records from your existing OBJ_TYPE and OBJ_TYPE_RLTNSHP tables. That wud help us to code it a lot easier.
nbuchAuthor Commented:
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

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
Jinesh KamdarCommented:
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.
strtbl VARCHAR2(100) := 'CREATE TABLE ';
strcol VARCHAR2(100) := '(name VARCHAR2(200) NOT NULL, description VARCHAR2(400) NULL';
strsql VARCHAR2(4000);
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';  
    strsql := strsql || ')';
END create_table;

Open in new window

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

nbuchAuthor Commented:

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?

Mark GeerlingsDatabase AdministratorCommented:
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?
nbuchAuthor Commented:
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?
Mark GeerlingsDatabase AdministratorCommented:
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.
nbuchAuthor Commented:
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 :).
Jinesh KamdarCommented:
Use the SHOW ERRORS command to see the details about the errors. You can also use this SELECT to check for errors.

FROM user_errors
Mark GeerlingsDatabase AdministratorCommented:
If you get the response: "Procedure Created with Compilation Errors" then it definitely did not (and cannot) execute.  If you want to use this approach, you first have to get the procedure to compile successfully.  But that is *NOT* a guarantee that it will execute successfully, when it has dynamic SQL inside it!  The complilation is just the first obstacle that you have to get past.  After it is compiled successfully, you can try to run it.  That is the first time when the dynamic SQL statement will be evaluated, and executed *IF* it has no syntax errors, and there are no space problems, etc. that it may encounter.

This approach of trying to create dynamic "create table..." statements inside a PL\SQL procedure is certainly not easy or trouble-free.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now