Reading clob variable line by line

Question

There is a list of values, which I get as an output from the shell. This list contains one "name" per line as shown below. It has more than 300 rows and is expected to get longer.

BeforeArchive_Field
BeforeArchive_Reservoir
BeforeArchive_Well
BeforeArchive_Wellbore
BeforeArchive_WellboreCompletion
BeforeArchive_WellboreContactInterval
ba_organization_part0
ba_organization_part2

I need to pass this list to a pl/sql procedure so it could parse it and operate each separate "name". Due to the length of the list, I think of creating a stored procedure which would take this list a a CLOB parameter.

The two questions are:

Is it an optimal way(format) of dealing with the list?

If so, is there a straightforward way of parsing this hell of a clob line by line?

I read through this Reading clob line by line with pl\sql answer but reckon that things can be less complicated in this certain case.

I am not familiar with this LOB formats and would appreciate any help.

Answer 1

It appears to be a good idea to me: calling a stored proc once with the clob and make this one call the other proc dedicated to handle a single line could save some I/O between your applicative server and the DB one.

I have a very simple procedure to split a clob line by line:

create table test (c clob);

insert into test (c) values (
'azertyuiop
qsdfghjklm
wxcvbn
');

select to_char(regexp_substr(test.c, '.+', 1, level)) pattern
from test
connect by level <= regexp_count(test.c, '.+');

which gives, as expected:

    PATTERN
1   azertyuiop
2   qsdfghjklm
3   wxcvbn

You can use this query in your 1st stored proc inside a for line in () to call your 2nd procedure line by line.