I have this data of 100K+ that is imported on daily basis into a table. The data is coming from spool file and I need to parse it to identify every couple of rows (i.e., 10-20) as a separate batch. So the spool file will be split into thousands of separate files. There is a value I can find at the beginning of each batch, say it is 'x'. But now I'm thinking how to make the process as efficient as possible.
I though of FOR cursor as follows
...
v_seq NUMBER;
BEGIN
SELECT batch_seq.nextval into v_seq from dual;
FOR i IN (SELECT id, line FROM spool_tab)
LOOP
UPDATE spool_tab
SET line = v_seq
WHERE id = i.id;
IFÂ i.line = 'x' THEN
SELECT batch_seq.nextval into v_seq from dual;
END IF;
END LOOP
...
END;
But won't this way engine switch between PL/SQL and SQL for every single row, and hence slow down the performance?
Is there a better way to give a batch number for the entire set of rows when reach the value 'x'?
For reference, the spool_tab structure looks like this:
| id | line | batch_no |
|---|
| 1 | x | |
| 2 | y | |
| 3 | y | |
| 4 | x | |
| 5 | y | |
| 6 | y | |
| 7 | x | |
So I want to give first 3 rows batch_no 1, and rows 4-6 batch_no 3 and so own (but not always 3 rows, it depends on the x value presence).