Trigger Problem Workaround Needed

I had a cool idea and damned if Oracle won't let me do it now. I'm trying to use SQLLoader to load some data into a table. This table has just a insert before trigger on it. However the flat-file and the table don't match exactly. The only difference is that the flat-file has, say, a column of a persons name yet the table represents that with a say a employee number.

Now I thought that I would just tell SQL-loader that the "Name" column was "Employee_Num" and use the trigger to "look up" the persons employee number and *substitute* it in the "Employee_Num" columns and ta-da! Since the trigger is a before trigger this should work. However, even though both the "Employee_Num" is a varchar this doesn't work because Oracle *sees* 'John Dennis' on the insert command and says that it won't fit into the a varchar(5) column.

It basically doesn't give the trigger a chance to "correct" this size problem.

One workaround it to add a "fake" Person_name column to the table an use it to populate the "Employee_Num" column. the trigger would always null out the Person_name column before insertion. This seems to be a sub-optimal solution but I really don't won't to create a TMP table.

Any idea or workarounds would appreciated! Thanks in advance!!

Frustrated,

John Dennis Received on Wed Jun 22 1994 - 00:08:57 CEST