Search code examples
sqloracle-databaseplsqlclob

Reading clob variable line by line

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.

Solution

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