Solved

Creatting tables dynamically

Posted on 2011-09-05
7
382 Views
Last Modified: 2013-12-19
Hi

I have a data in a table as follows:

Col1-Col2-Col3-Col4-Col5-Col6-Col7-Col8-Col9-Col10>>XXX>>YYY

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

Thx
Naren
0
Comment
Question by:knaren1975
7 Comments
 
LVL 15

Accepted Solution

by:
gplana earned 500 total points
Comment Utility
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:

DECLARE
  myRowData VARCHAR2(4000);
  col1, col2, col3, col4, col5, col6, col7, col8, col9, col10 VARCHAR(500);
  pos, pos2 NUMBER;
BEGIN
  -- 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
END;
/

Hope it helps.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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.

Regards.
0
 

Author Comment

by:knaren1975
Comment Utility
the no of cols will not be consistent, some time i will get 5 cols . some times 10 etc
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.

 
LVL 15

Expert Comment

by:gplana
Comment Utility
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.

Regards.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
How do you get the data types for the table?
0
 

Expert Comment

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

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

10 Experts available now in Live!

Get 1:1 Help Now