Expert: Elliot Mak - 6/16/2009

Question
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

Cheers
Elliot


---------- FOLLOW-UP ----------

QUESTION: Hi Elliot,
 Thanks for you reply. Actually the database I am using is Oracle 9.2. And whatever you had guessed is all right. Basically I am a Java developer and knows very little in oracle. Through my search on net I was able to try my hands on SQl Loader and External tables. Will you please elaborate on your steps more, so that I can try it at my end.

Thanks,
Rocky.

Answer
Good day Rocky

If you are using v9.2, you may not have the ability to create an external table.  I believe this is available from 10g onwards.  I don't have a 9i environment to help you out in this regards, but I can certainly based it on 10g.

Question #1:  Is it mandatory to use Oracle 9i?  10gR2 or even 11g is much better and provide more functionalities than 9i.

****************************************
Setup the external table (10g and above)
****************************************
Assume the following:
- Oracle User and Schema name is ALLEXPERTS
- Database name is EXPERTS
- The directory object is file_dir
- CSV file directory is /export/home/log
- The csv file name is ALLEXPERTS_CSV.log
- The table name is all_experts_tbl


1. Create a directory object in Oracle.  The directory will point to the directory where the file located.
conn sys/{password}@EXPERTS as sysdba;
CREATE OR REPLACE DIRECTORY file_dir AS '/export/home/log';

2. Grant the directory privilege to the user
GRANT READ ON DIRECTORY file_dir TO ALLEXPERTS;

3. Create the table
Connect as ALLEXPERTS user

create table ALLEXPERTS.all_experts_tbl
(txt_line varchar2(512))
organization external
(type ORACLE_LOADER
default directory file_dir
access parameters (records delimited by newline
fields
(txt_line char(512)))
location ('ALLEXPERTS_CSV.log')
);

This will create a table that links the data to a file.  Now you can treat this file as a regular table where you can use SELECT statement to retrieve the data.

****************************************
PL/SQL to create the data (PSEUDO code)
****************************************
CREATE OR REPLACE PROCEDURE new_proc IS

  -- Setup the cursor
  CURSOR c_main IS SELECT   *
         FROM   allexperts.all_experts_tbl;
         
  CURSOR c_first_row IS ALLEXPERTS_CSV.logSELECT   *
         FROM   allexperts.all_experts_tbl
         WHERE ROWNUM = 1;          
         
  -- Declare Variable
  l_delimiter_count      NUMBER;          
  l_temp_counter         NUMBER:=1;
  l_current_row         VARCHAR2(100);
  l_create_statements  VARCHAR2(1000);

BEGIN

  -- Get the first row
  -- Open the c_first_row and fetch the data into l_current_row
  -- Count the number of delimiter l_current_row and set the l_delimiter_count
  OPEN c_first_row;
  FETCH c_first_row INTO l_current_row;
  CLOSE c_first_row;
  
  l_delimiter_count := number of delimiter in l_current_row;
  
  -- Create the table with the right number of columns
  l_create_statements := 'CREATE TABLE csv_table ( ';
  
  WHILE l_temp_counter <= l_delimiter_count
  LOOP
     
     l_create_statement := l_create_statement || 'COL' || l_temp_counter || ' VARCHAR2(100)'
  
     l_temp_counter := l_temp_counter + 1;
     
     IF l_temp_counter <=l_delimiter_count THEN
        l_create_statement := l_create_statement || ',';
     END IF;
     
         
  END;
  
  l_create_statement := l_create_statement || ')';

  EXECUTE IMMEDIATE l_create_statement;
  
  -- Open the c_main to parse all the rows and insert into the table
  WHILE rec IN c_main
  LOOP
     
     -- Loop thru all the records and parse them
     ...
     ...
     ...
     
     -- Insert the data into the table created above
     ...
     ...
     ..
     
  END LOOP;

END;
/

If you have to use 9i where external table is not available, you may need to use a staging table to load the csv file into that table first!

This should gives you a pretty good idea how it works.  Hope it helps.  Let me know if you shall have additional information

Cheers
Elliot