Oracle, save/map csv string to a table using utl_file and external tables

I use a pl/sql procedure calling a webservice. This webservice returns me a large csv-string which I hold in a clob. Since I do not want to parse the csv by foot, I thought of using external tables. So what I need to do is storing the csv data in a corresponding table.

What I am doing at the moment is, that I store the clob using utl_file. the stored file is defined in a external table. Ok, when I am开发者_运维问答 the only user this works very well. But since DBs are multiuser I have to watchout if someone else is calling the procedure and overwriting the external table data source file. What is the best way avoid a mess in table data source? Or what is the best way to store a cvs-sting into a table?

Thanks Chris

You want to make sure that the procedure is run by at most one session. There are several ways to achieve this goal:

• The easiest way would be to lock a specific row at the beginning of your procedure (SELECT ... FOR UPDATE NOWAIT). If the lock succeeds, go on with your batch. If it fails it means the procedure is already being executed by another session. When the procedure ends, either by success or failure, the lock will be released. This method will only work if your procedure doesn't perform intermediate commits (which would release the lock before the end of the procedure).
• You could also use the DBMS_LOCK package to request a lock specific to your procedure. Use the DBMS_LOCK.request procedure to request a lock. You can ask for a lock that will only be released at the end of your session (this would allow intermediate commits to take place).
• You could also use AQ (Oracle queuing system), I have little experience with AQ though so I have no idea if it would be a sensible method.

Maybe you should generate temporary filename for each CSV? Something like:

SELECT TO_CHAR(systimestamp, 'YYYYMMDDHH24MISSFF') filename FROM dual

You can use UTL_FILE.FRENAME.

In similar situations, I have the external_table pointing to a file (eg "fred.txt"). When I get a new source file in, I use UTL_FILE.FRENAME to try to rename it to fred.txt. If the rename fails, then another process is running, so you return a busy error or wait or whatever.

When the file has finished processing, I rename it again (normally with some date_timestamp).