AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
Question Hello Sir,
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.
This whole thing has to be incorporated inside a stored procedure with upload of file.
Thanks,
Rocky
Answer Good day Rocky,
I will need to make some assumption based on your question.
1. A new table will be generated everytime it loads
2. These tables are permanent tables
3. These tables are not for temporary data loading
4. You are using Oracle 10gR2 and above
5. Different files may have different # of columns.
6. All the rows in the same file have the same # of columns.
Here is what I will do in this situation using PL/SQL
Setup
1. Create an external table that link the source of the table to a CSV. This external table contains only one column.
PL/SQL
1. In your PL/SQL, open a cursor for the external table
2. Get the first row from the external table and check # of columns in the table (by counting the comma delimiter)
3. Construct a create statement for the permanent table with the correct # of columns
4. Use EXECUTE IMMEDIATE command to execute the create statement above
5. Use a while loop to parse each rows from the external table and then insert it into the permanent table.
You can modularized each of those steps so that you can reuse them in your other PL/SQL program.
Of course, there may be another way to do it, but I will do it this way myself.
Hope it helps, and if you shall have additional questions, please don't hesitate to let me know
