Discussion of the Month:  Coding Guidance Requested

I am not really a programmer, but have been requested to modify a package. Turns out, there is quite a bit more to it than a simple modification.

After searching through the pl/sql conference here, I'm not sure the proposed method is actually the best.
Would someone please provide an expert opinion on which of the three options (or add another one, if appropriate) seems the best for this effort?

Problem: master/detail records coming from a flat file (created from a sqlserver database) to 2 tables in an Oracle 8.0.5 database. One of the columns in the detail record will have to be pulled from the Oracle database. If records exist in tables
already, overwrite.

Have to pull one column for Initial load estimate 200,000 rows. Subsequent loads s/b about 5,000 rows.

Options:

1. use a pl/sql package with utl_file to read in the data and process it. This was the first thought, but after reading some of the threads that mentioned performance problems with utl_file, I became concerned.

2. Load the data into two temporary tables, and just do inserts/updates as selects.

3. Get a real programmer to write a c (or whatever) program to do this.

I would really like to know which, if any, of the above solutions would be the most efficient, i.e. the best solution.  If it is 6 of one, 1/2 dozen of the other, that's fine.  I haven't written a thing since Cobol and some PL/I, so I'd really appreciate input from the experts.

Thanks a bunch,
Jan

I would go for the UTL_File in a package. The loader/two-table option would work too, but requires two steps -- one to run the loader, and then another to insert/update.

When you're done, you will be the 'Real Programmer'.

SC

There is also option 2b, "Use SQL*Loader to load the data into two temporary tables, and do inserts/updates via stored procedures" (depends how much manipulation & error handling you need). I've used this a number of times.

However I agree with Steve, since you don't have very big volumes to deal with you should give the UTL_FILE method a shot as it keeps all the code in one place and gives an Oracle command-line interface, even if it's not as fast as SQL*Loader at loading the file.

It would be worth structuring the load procedures so it wouldn't be too hard to switch over to a SQL*Loader approach at a later date, if you found you needed the file loading performance (i.e. have a parameterised "load one record" procedure & call it for each line in the file). I've done this before and actually needed both, as a second data feed showed up - it was great to be able to call the same "CREATE_TRANSACTION" function from both modules.

The obvious problem with UTL_FILE is that it cannot list/rename/delete/move files etc. This meant previously that although it was fine for actually loading files, you generally ended up with OS script as well to check for/rename/delete files etc.

Now that we have Java SPs in the DB, many more file operations are available natively within Oracle, making it more feasible to write some kind of generic file loader exclusively inside the DB. Check out SF's XFile Java Class in the archives to see how these operations can be done in Java.

Having said that, as you pointed out, SQL*Loader is significantly faster than UTL_FILE - as Will says, judging by your file size, this is probably not too much of an issue. Also, by writing file loader in the DB, you throw away all of SQL*Loader's built in functionality.

Padders

Thank all of you, so much, for your replies. It has certainly helped me.
I'll give the utl_file a go, and see what happens.

Many thanks to all in both pipelines. I'm constantly amazed by the amount and depth of knowledge out there, and the generosity of those who share what they know.

Best Regards,
Jan