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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
asiminatorAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.