pl sql procedure to lookup column names

Hi

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
                        chan2
                        chan3

a file i reveive might look like this:

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

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
asiminatorAsked:
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.
0
 
MilleniumaireCommented:
Hi,
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.
0
 
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.
0
 
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.
declare
      v_headerrow   cardata_ext%rowtype;
      v_dbheaders   cardata_ext%rowtype;
begin
 
    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; 
 
end;

Open in new window

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

From novice to tech pro — start learning today.