I'm writing data from Oracle Db to MongoDB via http calls (POST, PUT). The data is changing and I'm grabbing it via a cursor and then inside the for loop I'm doing the http call (one call per iteration). MongoDb does not allow bulk write, so I have to send one row per iteration. This takes very long time (e.g., 30 minutes to write 3K rows).
Inspired by the answer of this question I figured it out that the process can be more efficient using DBMS_PARALLEL_EXECUTE. However, this part "Run each thread (parallel job process) using a standard FOR cursor loop (e.g. for c in(select...) loop .. end loop;) - and inside the loop do the HTTP calls" is not clear to me.
I have the following syntax:
FOR c IN
(SELECT......)
LOOP
--http calls
END LOOP;)
I tried to read the examples here but I could not figure out the following:
- Based on what I should create chunks in my case (ROWID, Number col, or SQL)?
- Where should I create task? At the beginning of my procedure? Inside the loop? And where I create chunks? Inside the loop?
- How about DBMS_PARALLEL_EXECUTE.run_task ?
- What is the chunck size? I understand I can only open 5 http calls max at the same time
Your help is deeply appreciated.
P.S. I preferred to open new thread, as the discussion will be about DBMS_PARALLEL_EXECUTE rather than cursors best practices.