Search code examples
plsqloracle11gregexp-substr

oracle regexp_instr in pl/sql doesn't work

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
Solution

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