I’m in the process of re-engineering a legacy process that receives flat file data and loads it into a database on a nightly basis. As an interim step in the migration I need to continue to pick up this flat file and load it into a new Oracle 10g DB. There are about 80,000 rows of data in the flat file. Obviously I would want to batch insert these rows but there is a twist. I’m loading them into a staging table that has a “row id” number field that in constrained to unique numbers. I have a sequence object that I need to call in order to get this number. I have not been able to figure out how to do this with a batch insert. I have tried several forms of “select seq.nextval from dual” as a literal input to that column in the batch insert service, but no dice, it doesn’t seem to be able to handle this. The only thing I seem to be able to do is call a custom SQL statement for each row to get the id and do single insert.
We’re using the older jdbc adapter, 6.0.3. I’ve been pushing to upgrade the adapter but I can’t do it myself and I’m running out of time. Also, the DB is designed by IBM and I’d have a hard time getting changes made.
I’m just wondering if anyone has seen this before and came up with a good solution. I’ve thought about just doing it manually with a java service but the more I think about it I’m wonder if I would still have the same problem anyway.
Thanks in advance!