1

I have a table with 20 rows. Each row has a blob field that holds a txt file. Inside the text file is a million unique 10 digit numbers. How can I read and insert those numbers into another table in an efficient manner using oracle sql and plsql.

Description of what I'm doing now:

  1. I select the 20 rows.
  2. For each row, I open the text file and read each line and store the number into a nested table collection.
  3. I run a forall loop to insert those numbers along with some other fields into a table.

It works but takes a really long time. Is there a better way?

thank you!

APC
  • 144,005
  • 19
  • 170
  • 281
Hoja0512
  • 11
  • 1
  • 1
    You could create the files first with `PL/SQL` and then use `SQL*Loader` to load the data. – Maheswaran Ravisankar Mar 16 '17 at 03:09
  • How long is "a really long time"? Given you are assembling and inserting twenty million rows, what do you consider a reasonable amount of time? – APC Mar 16 '17 at 04:36
  • Also, how are you reading the LOB file? The internals matter here. – APC Mar 16 '17 at 05:02
  • Could you compare the execution of your code with SQL*Loading of the files alone. This latest should be a lower limit of what you can achieve yourself with PL/SQL alone. – J. Chomel Mar 16 '17 at 07:49

0 Answers0