Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Creatting tables dynamically

Posted on 2011-09-05
Medium Priority
Last Modified: 2013-12-19

I have a data in a table as follows:


I need to create a table from the above table by splitting the 10 columns based on - seperator. Please suggest how to do it

Question by:knaren1975
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 15

Accepted Solution

gplana earned 2000 total points
ID: 36483275
I would create the structure of the table manually.

When created, you should create a program which reads every line of the raw data, process the text and then INSERT into new table.

You can create this program as a PL/SQL block. Something like this:

  myRowData VARCHAR2(4000);
  col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 VARCHAR(500);
  pos, pos2 NUMBER;
  -- get your row data into variable myRowData (maybe with a SELECT and a cursor for each line ?

  pos = InStr(myRowData,'-');
  col1 = SubStr(myRowData,1,pos-1)

  pos2 = InStr(myRowData,'-',pos+1);
  col2 = SubStr(myRowData,pos+1,pos2-pos);
  pos = pos2

  pos2 = InStr(myRowData,'-',pos+1);
  col3 = SubStr(myRowData,pos+1,pos2-pos);
  pos = pos2

  pos2 = InStr(myRowData,'-',pos+1);
  col4 = SubStr(myRowData,pos+1,pos2-pos);
  pos = pos2

-- ... and so on

Hope it helps.
LVL 15

Expert Comment

ID: 36483278
Also, of course you will need a while for every rowdata line (probably combined with a cursor) and you need the INSERT sentence after getting all cols.


Author Comment

ID: 36483616
the no of cols will not be consistent, some time i will get 5 cols . some times 10 etc
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 15

Expert Comment

ID: 36483694
In this case you can use DBMS_SQL package to dynamically execute a "CREATE TABLE" sentence.
Also you can detect if the returned value of InStr function is 0. In this case it means the text ('-') hasn't found.

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36488462
How do you get the data types for the table?

Expert Comment

ID: 36539545
For the data types and sizes, it's simplest just to guess and set them all the VARCHAR(2000).  If you need to perform arithmetic operations on them, you can let Oracle do automatic data conversion for you, which will work much of the time (until you need it most).
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36539629
If you are just going to 'guess' set it to the MAX of 4000 not 2000 unless you are running multi-byte character sets.  Also use varchar2:  varchar2(4000).

But that might break easily if you need more.  Then use a CLOB.

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

604 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