Solved

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?

Posted on 2007-12-01
16
1,051 Views
Last Modified: 2013-12-18
Hello,
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.
 

PLEASE NOTE: OBJ_TYPE_NAMES ARE IN THE obj_type TABLE ONLY****

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 

<DESIGN_SPECIFICATION> Based on THE FIRST ID NUMBER "OBJ_TYPE_ID" = 1
 

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
 
 

Table Name:OBJ_TYPE_RLTNSHP

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

0
Comment
Question by:nbuch
  • 6
  • 5
  • 3
16 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 

Author Comment

by:nbuch
Comment Utility
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
);
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
Please post the records from your existing OBJ_TYPE and OBJ_TYPE_RLTNSHP tables. That wud help us to code it a lot easier.
0
 

Author Comment

by:nbuch
Comment Utility
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
);
 
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
Comment Utility
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;

Open in new window

0
 

Author Comment

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

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:nbuch
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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?
0
 

Author Comment

by:nbuch
Comment Utility
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?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 

Author Comment

by:nbuch
Comment Utility
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 :).
0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 250 total points
Comment Utility
Use the SHOW ERRORS command to see the details about the errors. You can also use this SELECT to check for errors.

SELECT *
FROM user_errors
WHERE NAME = 'YOUR_SP_NAME';
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now