Strategy Question

, ,
1

Hi All,
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!

-Scott

2

Scott,

I just gave it a try and it seems doable. Here’s what you do:

  1. Open your BatchInsertSQL adapter service
  2. Go the INSERT tab
  3. Change the Expression for the “row id” column to <sequence_name>.nextval

Let me know if this works for you.

  • Percio
3

It must be something with the database then. I tried the basic seq.nextval statement but would always get strange errors like “missing comma”, etc. I have zero visibility into this database. I’m crossing at least two firewalls and my only info is what I can gleam through the webMethods adapter. I’ll just have to make due with what I have. Thanks for looking at it, I mainly wanted to make sure I wasn’t going crazy.

Thanks again,

Scott McMahon

4

Post the complete error message here. Maybe we can help further.