Home » SQL & PL/SQL » SQL & PL/SQL » Creating dynamic table (Oracle,9.2,Windows)
Creating dynamic table [message #408196] Mon, 15 June 2009 04:30 Go to next message
Rocky9
Messages: 6
Registered: June 2009
Junior Member
Hello Everybody,
I want a help in creating dynamic table as per the csv file. The csv file which is to be uploaded does not have a fixed format (No. of columns). So I want to create table with the same number of column as present in the csv file.

Thanks,
Rocky
Re: Creating dynamic table [message #408201 is a reply to message #408196] Mon, 15 June 2009 04:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Create an external table with 1000 columns. All are optional.
But I can already predict your next question: how to address these columns dynamically.
Answer: prepare for nightmares. If you get to get some sleep at all, that is.

[Updated on: Mon, 15 June 2009 04:45]

Report message to a moderator

Re: Creating dynamic table [message #408214 is a reply to message #408196] Mon, 15 June 2009 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Rocky9, what business requirement stands behind your question?
Re: Creating dynamic table [message #408225 is a reply to message #408196] Mon, 15 June 2009 06:53 Go to previous messageGo to next message
Rocky9
Messages: 6
Registered: June 2009
Junior Member
Hi,
Thanks, for answering my query. My problem is that after the user uploads the csv file, I have to populate the csv file in the table. Also the uploaded csv file doesn't have fix number of columns. This whole thing I have to complete inside Stored Procedure. So, I just want to know how could I achieve this? I guess I might have clear my problem.

Thanks,
Rocky
Re: Creating dynamic table [message #408232 is a reply to message #408225] Mon, 15 June 2009 07:03 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you saying that there's no standard in file format? Does that mean that users are allowed to send any kind of CSV files? How can you tell which CSV file value belongs to which table and column?
Re: Creating dynamic table [message #408239 is a reply to message #408196] Mon, 15 June 2009 07:21 Go to previous messageGo to next message
Rocky9
Messages: 6
Registered: June 2009
Junior Member
hi,
As I said the number of columns are not constant.
For eg: One csv file can have 4 columns.
Second csv file can have 7 columns.
I just want to store the contents of this csv in some table, and thereafter use this table to analyze this data and store required data. After the analyzing, I can drop the table in which the contents of csv file were stored.

Thanks,
Rocky
Re: Creating dynamic table [message #408258 is a reply to message #408196] Mon, 15 June 2009 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>thereafter use this table to analyze this data and store required data
How do you "anaylze & store data" when you do not know column name or data type of this "data" since it is so "dynamic"?

Garbage In, Garbage Out (GIGO)!
Re: Creating dynamic table [message #408372 is a reply to message #408196] Mon, 15 June 2009 23:50 Go to previous messageGo to next message
Rocky9
Messages: 6
Registered: June 2009
Junior Member
Hi,
My idea behind this is that I would read the header of the file first, and then I would create the table with the datatype set to varchar for all columns. This table can then be dropped after loading the data in the required table after analyzing.

Thanks,
Rocky

[Updated on: Mon, 15 June 2009 23:51]

Report message to a moderator

Re: Creating dynamic table [message #408373 is a reply to message #408196] Tue, 16 June 2009 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>My idea behind this is that I would read the header of the file first, and then I would create the table with the datatype set to varchar for all columns. This table can then be dropped after loading the data in the required table after analyzing.

OK, proceed & do so.
Re: Creating dynamic table [message #408381 is a reply to message #408196] Tue, 16 June 2009 01:08 Go to previous messageGo to next message
Rocky9
Messages: 6
Registered: June 2009
Junior Member
Can I achieve this inside Stored Procedure? Any idea..I am stuck with this...I am a newbie to Oracle
Re: Creating dynamic table [message #408382 is a reply to message #408381] Tue, 16 June 2009 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I am a newbie to Oracle

Perhaps you should talk to a senior member of your team and discuss the issue. Because, idea you got doesn't smell good.
Re: Creating dynamic table [message #408383 is a reply to message #408196] Tue, 16 June 2009 01:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can I achieve this inside Stored Procedure? Any idea..I am stuck with this...I am a newbie to Oracle

The proof is in the results.

Knock yourself up!
Re: Creating dynamic table [message #408658 is a reply to message #408196] Wed, 17 June 2009 04:41 Go to previous message
Rocky9
Messages: 6
Registered: June 2009
Junior Member
Thanks Everybody,
I was able to generate dynamic table. Thanks again for your support.

Thanks,
Rocky.
Previous Topic: Multiple insert for the same row
Next Topic: Spilliting row
Goto Forum:
  


Current Time: Thu Dec 08 02:27:51 CST 2016

Total time taken to generate the page: 0.11063 seconds