Problem trying to write a PL/SQL routine that will generate a CREATE TABLE statement

[nanabuch]

Hello,

I am new to this forum, and I am a newbit in Oracle, I do have a background in MS Access, VBA, SQL server development and some Oracle backend development work.

I have been giving a task to dynamically create tables through code based on parameters in existing tables.

I will try to keep this short and to the point

The goal is to extract values from existing tables I will give an example of two tables to keep it simple

Table 1
Name: Obj_Type
Columns
obj_type Id obj_type_nm
1 Data_Design_Spe c
2 Logical_Data_Mo del
3 Entity

Table 2
Name: Property
Columns
prpt_id prprt_nm
1 Source
2 Discoverable
3 Required

The parameters and logic for this is to use a where clause where 1 = would return Data_Design Spec as the NEW Table name and extract data from Table 2 and extract 1 = would be the property name in the new table which would be Source from Table 2

This has to be in some type of routine, I cannot manually use CREATE TABLE statement this has to be done in a SQL statement, also this routine will generate new tables based on values from EXISTING to inlcude data types and attributes. I will GLADLY pay someone through pay pal for the results or who can help me with this....thanks so much

Nana

[amitpatel66]

HI Nana,

Welcome to TSDN!!

Please make seure you follow POSTING GUIDELINES when ever you post in this forum.

Thanks
MODERATOR

[amitpatel66]

Hello,

I am new to this forum, and I am a newbit in Oracle, I do have a background in MS Access, VBA, SQL server development and some Oracle backend development work.

I have been giving a task to dynamically create tables through code based on parameters in existing tables.

I will try to keep this short and to the point

The goal is to extract values from existing tables I will give an example of two tables to keep it simple

Table 1
Name: Obj_Type
Columns
obj_type Id obj_type_nm
1 Data_Design_Spe c
2 Logical_Data_Mo del
3 Entity

Table 2
Name: Property
Columns
prpt_id prprt_nm
1 Source
2 Discoverable
3 Required

The parameters and logic for this is to use a where clause where 1 = would return Data_Design Spec as the NEW Table name and extract data from Table 2 and extract 1 = would be the property name in the new table which would be Source from Table 2

This has to be in some type of routine, I cannot manually use CREATE TABLE statement this has to be done in a SQL statement, also this routine will generate new tables based on values from EXISTING to inlcude data types and attributes. I will GLADLY pay someone through pay pal for the results or who can help me with this....thanks so much

Nana

Nana,

This forum is to help peoples with all their technical problems in different technologies.
We are glad to help peoples finding solution for their issues and we are not here to do business :)
So you can feel free and ask us anything in any technology.

So coming to your problem, What I understand from your explanation is:

If extract = 1 THEN new table should be created with name data_design_spe c ie

[code=oracle]
-- This is just a pseudo code and not exact oracle source code

IF (extract = 1) THEN
CREATE table data_design_spe c;
[/code]

Now could you please explain the second part ie what you need to do with the "source" from table 2??

[nanabuch]

Nana,

This forum is to help peoples with all their technical problems in different technologies.
We are glad to help peoples finding solution for their issues and we are not here to do business :)
So you can feel free and ask us anything in any technology.

So coming to your problem, What I understand from your explanation is:

If extract = 1 THEN new table should be created with name data_design_spe c ie

[code=oracle]
-- This is just a pseudo code and not exact oracle source code

IF (extract = 1) THEN
CREATE table data_design_spe c;
[/code]

Now could you please explain the second part ie what you need to do with the "source" from table 2??

Thanks for the response :)

This should be the end result of the new table structure

create table "OBJ_TYPE"
name varchar2(100) not null,
description varchar2(400) null,
and so forth.....

The ONLY columns that are to be hard coded are NAME and DESCRIPTION for each new table and the other columns are to be dynamically created based on the values in exisiting tables

Based on the EXISTING table structure I need to create NEW tables dynamically through code based on conditions of the EXISTING tables.

Example of the EXISTING tables are

Table 1
Name: Obj_Type
Columns
obj_type Id obj_type_nm
1 Data_Design_Spe c
2 Logical_Data_Mo del
3 Entity

Table 2
Name: Property
Columns
prpt_id prprt_nm
1 Source
2 Discoverable
3 Required

Table 3
Name: Attribute
Columns
1. ERP_NO
2. Revision
3. Release
Datatype Column
1. String
Property Datatype Size
1. 2000

Example I would need to pull out from EACH table the column name based on condition EXAMPLE ERP_NO =1, Datatype Column, String =1 and Property Datatype Size =1 which would be 2000, Please note there are more data in the columns and fields, but I just wanted to include only a small portion of the data as an example.

the output would be something like this it would return a NEW TABLE STRUCTURE like the example below..based on SQL code.

Create table "Attribute"

Name varchar2(100)
Description varchar2(400)
Datatype String(20)

[nanabuch]

One more thing I forgot to mention the NEW table NAMES cannot be hard coded, the CODE would have to look in the EXISITNG Table and in the OBJ_type TABLE and look for the STRING "DATA DESIGN SPECIFICATION" and somehow with a SQL statement USE the create table function to NAME the new table "DATA DESIGN SPECIFICATION" and then the code will continue to loop through the rest of the tables and add column names based on the values in each table that is equal to based on the obj_id number 1 or 2 and so forth....