pl sql procedure to lookup column names


I have a problem where i am trying to load data using external tables but the columns that i get can vary and they have different names so i need to have lookup to translate the names i receive in the header row to the corresponding column names in the table.

My external table looks like this:
cardata_ext:   chan1

a file i reveive might look like this:

 time, speed, height
12:20, 56, 10
12:21, 58, 9

the probelm is the column headers in the first line of the file dont match the fields in my final table so i have a lookup table

inputname, dbname
speed, velocity
time, thedatetime
height, altitude

My final table has a lot more columns so i need to build a dynamic insert statement that looks something like:

insert into cardata(thedatetime,velocity,altitude) (select chan1,chan2,chan3 from cardata_ext);

Please help
Who is Participating?
asiminatorConnect With a Mentor Author Commented:
the solution was to query the table to get a list of columns and then build a dynamic sql query with a bunch of union all's to join em together.
You could build up an insert statement in a string and then use the EXECUTE IMMEDIATE statement to run it.

The complexity of your code will be in building up the statement.

You could use the utl_file package to read the first line of data for the external file to determine the column headings, map these to your column names and then build your insert statement.
asiminatorAuthor Commented:
im actually looking for more help than that.  I have the headers in a  cardata_ext%rowtype variable.  What i want is a cardata_ext%rowtype variable with the mapped column names so that i can build the insert statement.
asiminatorAuthor Commented:
this is the sort of thing i am looking for but the following code doesnt compile... its just to get an idea of what im after.
      v_headerrow   cardata_ext%rowtype;
      v_dbheaders   cardata_ext%rowtype;
    select * into v_headerrow from cardata_ext  where TheDate like '%DATE%' and rownum = 1;
for c in (select column_name from user_tab_columns where table_name = 'CARDATA_EXT') loop
   execute immediate ('select dbname into v_dbheaders.' || c.column_name || ' from VARLOOKUP where sourcename = v_headerrow.' || c.column_name )
                       into  v_dbheaders.c.column_name;
end loop; 

Open in new window

asiminatorAuthor Commented:
I have ansered the question myself.  Thanks anyways.
All Courses

From novice to tech pro — start learning today.