PLSQL Returning Multiple Lines From user_source

Question

some_expression;
another_expression;
    raise_application_error(-20100
                   , 'Explanation message.');
expression;

The column has the text above. The text contains raise_application_error function call in more than one lines. I want to get the entire call by RegEX.

SELECT
  t.name,
  regexp_substr(t.TEXT, 'raise_application_error.*' || CHR(10) || '.*', 1, 1, 'm') as text
FROM user_source t
WHERE t.TEXT LIKE '%raise_application_error%';

The SQL code above returns the only first line: 'raise_application_error(-20100' but I want the entire call. I thought that '|| CHR(10)' would solve the problem but it didn't.

NAME	TEXT
TEXT_I_DO_NOT_WANT	raise_application_error(-20100
TEXT_I_WANT	raise_application_error(-20100, 'Explanation message.');

Answer 1

Don't use regular expressions, just use normal string functions:

SELECT name,
       substr(
         TEXT,
         INSTR(text, 'raise_application_error')
       ) as text
FROM   user_source
WHERE  TEXT LIKE '%raise_application_error%';

---

I thought that '|| CHR(10)' would solve the problem but it didn't.

That is because each row of the USER_SOURCE table contains a single line so you cannot match multiple lines within a single row because that does not exist. What you need to do is find the first row and get all the subsequent rows.

If you want multiple rows then, from Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row matching and find the first matching row and extract the remaining rows and then you can aggregate:

SELECT name,
       LISTAGG(
         CASE cls
         WHEN 'HAS_ERROR'
         THEN SUBSTR(text, INSTR(text, 'raise_application_error'))
         ELSE text
         END
       ) WITHIN GROUP (ORDER BY line) AS text
FROM   user_source
MATCH_RECOGNIZE (
  PARTITION BY name
  ORDER BY line
  MEASURES
    MATCH_NUMBER() AS match,
    CLASSIFIER()   AS cls
  ALL ROWS PER MATCH
  PATTERN (has_error rest* $)
  DEFINE
    has_error AS TEXT LIKE '%raise_application_error%'
)
GROUP BY name, match

db<>fiddle here