oracle regexp_instr in pl/sql doesn't work

Question

I'm having a clob with pl/sql code inside.
I have to check if there is no exit command inside at the end.

So the following is not allowed:

Some code ...
exit

Or

Some code ...
exit;

Or

Some code ...
exit
/

But the following is allowed:

Some code ...
exit when ... Some code ...

Or

Some code ...
Some other code ... -- If this happens than exit
Some code ...

I've tried with the following piece of code, but that doesn't work:

if regexp_instr(v_clob, chr(10) || 'exit[;]?[^[[:blank:]]]', 1, 1, 0, 'i') != 0 then

Alex Poole · Accepted Answer · 2020-07-31 08:09:27Z

1

You have two many square brackets, and you aren't matching line end; this works with your examples:

regexp_instr(v_clob, chr(10) || 'exit(;)?([^[:blank:]]|$)', 1, 1, 0, 'i')

As you want to match at the start of a line it would be a bit simpler (and possibly safer for catching both LF and CRLF/LFCR) to do:

regexp_instr(v_clob, '^exit(;)?([^[:blank:]]|$)', 1, 1, 0, 'im')

db<>fiddle showing your sample values in a CTE and your original result plus the output of both of these options.

answered Jul 31, 2020 at 8:09

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1