1

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

Improve this question

3 Answers 3

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

SELECT TO_CHAR(systimestamp, 'YYYYMMDDHH24MISSFF') filename FROM dual
Improve this answer

1 Comment

That won't work with the external table solution. (Although we can dynamically change the source file for an external table, it doesn't solve the problem of multiple users running the thing at the same time).
0

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).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.