Creatting tables dynamically

knaren1975
knaren1975 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.

Author

Commented:
the no of cols will not be consistent, some time i will get 5 cols . some times 10 etc
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
How do you get the data types for the table?

Commented:
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).
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial