sql - regex for oracle "create procedure" definition -

i need regex capture full "create procedure" statement.

here 1 of examples, used testing regex:

create or replace procedure sp_for_comp (p_varname in varchar2, p_value in out number)       v_if_exists number(10,0); begin    select   count(*) v_if_exists pkg_var variablename = p_varname;    if v_if_exists > 0          begin          select value p_value pkg_var variablename = p_varname;          exception          when others             null;       end; else       begin          insert pkg_var  values(p_varname, p_value);          exception          when others             null;       end;    end if; end; / 

current regex:

/create\s+(or\s+replace\s+)?procedure\s+(\w+)\s*\(((?!.*\bend\b\s*(\w+\s*)?\;\s*\/).*\s*)+.+/ 

as issue: use qregularexpression class , program failed when run on large files. also, when run on small file - works correctly.

after lot of tests on online debuggers, regexr.com, not find problem in regex.

how should change , may problems?

try simple like:

create(\s+or\s+replace)\s+procedure.*?end;\s*/ 

it looks create or replace procedure @ start , end end; followed / (indicating end of pl/sql block in sql scope) on next line minimal amount between.

(note: want use ni regular expression match parameters allow . match newline character , case-insensitive matches.)

example:

create table script (value ) select  'create or replace procedure sp_for_comp (p_varname in varchar2, p_value in out number)' || chr(10)         || '   as' || chr(10)         || '   v_if_exists number(10,0);' || chr(10)         || 'begin' || chr(10)         || '   select   count(*) v_if_exists pkg_var variablename = p_varname;' || chr(10)         || '   if v_if_exists > 0' || chr(10)         || '   then' || chr(10)         || '      begin' || chr(10)         || '         select value p_value pkg_var variablename = p_varname;' || chr(10)         || '         exception' || chr(10)         || '         when others then' || chr(10)         || '            null;' || chr(10)         || '      end;' || chr(10)         || 'else' || chr(10)         || '      begin' || chr(10)         || '         insert pkg_var  values(p_varname, p_value);' || chr(10)         || '         exception' || chr(10)         || '         when others then' || chr(10)         || '            null;' || chr(10)         || '      end;' || chr(10)         || '   end if;' || chr(10)         || 'end;' || chr(10)         || '/' dual;  select count(*)   script  regexp_like( value, '^create(\s+or\s+replace)\s+procedure.*?end;\s*/$', 'n' ); 

outputs:

count(*) --------        1