Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE PROCEDURE procedure_name
IS
BEGIN
  raise_application_error(-20100, 'Something bad!');
END;
/
SELECT *
FROM   user_source;


NAME
TYPE
LINE
TEXT
ORIGIN_CON_ID


PROCEDURE_NAME
PROCEDURE
1
PROCEDURE procedure_name

3

PROCEDURE_NAME
PROCEDURE
2
IS

3

PROCEDURE_NAME
PROCEDURE
3
BEGIN

3

PROCEDURE_NAME
PROCEDURE
4
  raise_application_error(-20100, 'Something bad!');

3

PROCEDURE_NAME
PROCEDURE
5
END;

3


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


NAME
TEXT


PROCEDURE_NAME
raise_application_error(-20100, 'Something bad!');



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


NAME
TEXT


PROCEDURE_NAME
raise_application_error(-20100, 'Something bad!');
END;

NAME	TYPE	LINE	TEXT	ORIGIN_CON_ID
PROCEDURE_NAME	PROCEDURE	1	PROCEDURE procedure_name	3
PROCEDURE_NAME	PROCEDURE	2	IS	3
PROCEDURE_NAME	PROCEDURE	3	BEGIN	3
PROCEDURE_NAME	PROCEDURE	4	raise_application_error(-20100, 'Something bad!');	3
PROCEDURE_NAME	PROCEDURE	5	END;	3