While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.
Colleage to the rescue:
Cool!
ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );
As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.
As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.
As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?
Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!
So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.
5 comments:
>> So I couldn't use that new thing I learned, oh well.
Why did you have to use ROLLBACK? If you had plumped for DELETE or TRUNCATE you could still have used that new thing. Obviously if the tables in question have existing data you want to retain then TRUNCATE is right out, but DELETE would still be doable.
Cheers, APC
Good point.
I should have clarified a bit more.
The COMMIT was occuring mid-transaction, in other words, before it was complete. So it was undesired behavior. I wanted to process the entire group of files before COMMITting them.
What about using Automnomous transactions to do the execute immediate in a separate section? Then you return to your existing transaction? That way you can still rollback your data?
Do this:
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php
Yes, that would have worked, hadn't thought about it.
I tend to only think of autonomous transactions in the context of logging so it never occurred to me.
Post a Comment