Enhance annotation processing by improving SQL text handling and addi…

…ng header stripping functionality
utPLSQL · jgebal · Apr 22, 2026 · Mar 9, 2026 · Mar 9, 2026 · Mar 9, 2026
commit 40eb166c1a228b7d2ced30a471c8dd41335b8cb7
diff --git a/source/core/annotations/ut_annotation_manager.pkb b/source/core/annotations/ut_annotation_manager.pkb
@@ -231,23 +231,25 @@
 
     function get_source_from_sql_text(a_object_name varchar2, a_sql_text ora_name_list_t, a_parts binary_integer) return sys_refcursor is
       l_sql_clob    clob;
-      l_sql_lines   ut_varchar2_rows := ut_varchar2_rows();
+      l_sql_lines   dbms_preprocessor.source_lines_t := dbms_preprocessor.source_lines_t();
+      l_sql_lines_clob ut_varchar2_list := ut_varchar2_list();
       l_result      sys_refcursor;
+      l_replaced    boolean := false;
     begin
       if a_parts > 0 then
         for i in 1..a_parts loop
           ut_utils.append_to_clob(l_sql_clob, a_sql_text(i));
         end loop;
-        l_sql_clob := ut_utils.replace_multiline_comments(l_sql_clob);
-        -- replace comment lines that contain "-- create or replace"
-        l_sql_clob := regexp_replace(l_sql_clob, '^.*[-]{2,}\s*create(\s+or\s+replace).*$', modifier => 'mi');
-        -- remove the "create [or replace] [[non]editionable] " so that we have only "type|package|procedure|function" for parsing
-        -- needed for dbms_preprocessor
-        l_sql_clob := regexp_replace(l_sql_clob, '^(.*?\s*create(\s+or\s+replace)?(\s+(editionable|noneditionable))?\s+?)((package|type|procedure|function).*)', '\5', 1, 1, 'ni');
-        -- remove "OWNER." from create or replace statement.
-        -- Owner is not supported along with AUTHID - see issue https://github.com/utPLSQL/utPLSQL/issues/1088
-        l_sql_clob := regexp_replace(l_sql_clob, '^(package|type|procedure|function)\s+("?[[:alpha:]][[:alnum:]$#_]*"?\.)(.*)', '\1 \3', 1, 1, 'ni');
-        l_sql_lines := ut_utils.convert_collection( ut_utils.clob_to_table(l_sql_clob) );
+
+        l_sql_lines_clob := ut_utils.clob_to_table(l_sql_clob);
+        for i in 1..l_sql_lines_clob.count loop
+          l_sql_lines(i) := l_sql_lines_clob(i);
+        end loop;
+
+        -- replace multiline comments that contain "-- create or replace" with single line comment to avoid parsing issues with dbms_preprocessor
+        l_sql_lines := ut_utils.replace_multiline_comments(l_sql_lines);
+        -- strip CREATE header (possibly split across lines) while preserving line numbers
+        l_sql_lines := ut_utils.strip_create_header_lines(l_sql_lines);
       end if;
       open l_result for
         select /*+ no_parallel */ a_object_name as name, column_value||chr(10) as text from table(l_sql_lines);

diff --git a/source/core/ut_utils.pkb b/source/core/ut_utils.pkb
@@ -682,10 +682,10 @@
    end if;

    -- Fast pre-scan to check for presence of multi-line comments; if none, return original source unmodified
    for i in 1 .. a_source.count loop
      if instr(a_source(i), '/*') > 0 then
        l_has_ml_comment := true;
        exit;
      end if;
    end loop;

@@ -693,7 +693,7 @@
      return a_source;
    end if;

    for i in 1 .. a_source.count loop
      l_line := a_source(i);

      -- Fast path: inside multi-line comment
@@ -723,8 +723,8 @@
      l_line      := null;

      <<scan_line>>
      loop
        exit when l_remaining is null or l_remaining = '';
        l_ml_start := instr(l_remaining, '/*');
        l_comment_start := instr(l_remaining, '--');
        l_text_start := instr(l_remaining, '''');
@@ -785,13 +785,13 @@
          -- Regular string literal: l_remaining starts at the opening quote
          -- scan from position 2 to skip the opening quote
          l_end := 2;
          loop
            l_end := instr(l_remaining, '''', l_end);
            exit when l_end = 0;
            if substr(l_remaining, l_end, 2) = '''''' then
              l_end := l_end + 2;  -- skip escaped quote pair
            else
              exit;                -- real closing quote
            end if;
          end loop;

@@ -812,94 +812,62 @@
     return l_result;
   end replace_multiline_comments;
 
-  function replace_multiline_comments(a_source clob) return clob is
-    l_result                  clob;
-    l_ml_comment_start        binary_integer := 1;
-    l_comment_start           binary_integer := 1;
-    l_text_start              binary_integer := 1;
-    l_escaped_text_start      binary_integer := 1;
-    l_escaped_text_end_char   varchar2(1 char);
-    l_end                     binary_integer := 1;
-    l_ml_comment              clob;
-    l_newlines_count          binary_integer;
-    l_offset                  binary_integer := 1;
-    l_length                  binary_integer := coalesce(dbms_lob.getlength(a_source), 0);
-  begin
-    l_ml_comment_start := instr(a_source,'/*');
-    l_comment_start := instr(a_source,'--');
-    l_text_start := instr(a_source,'''');
-    l_escaped_text_start := instr(a_source,q'[q']');
-    while l_offset > 0 and l_ml_comment_start > 0 loop
-
-      if l_ml_comment_start > 0 and (l_ml_comment_start < l_comment_start or l_comment_start = 0)
-        and (l_ml_comment_start < l_text_start or l_text_start = 0)and (l_ml_comment_start < l_escaped_text_start or l_escaped_text_start = 0)
-      then
-        l_end := instr(a_source,'*/',l_ml_comment_start+2);
-        append_to_clob(l_result, dbms_lob.substr(a_source, l_ml_comment_start-l_offset, l_offset));
-        if l_end > 0 then
-          l_ml_comment     := substr(a_source, l_ml_comment_start, l_end-l_ml_comment_start);
-          l_newlines_count := length( l_ml_comment ) - length( translate( l_ml_comment, 'a'||chr(10), 'a') );
-          if l_newlines_count > 0 then
-            append_to_clob(l_result, lpad( chr(10), l_newlines_count, chr(10) ) );
-          end if;
-          l_end := l_end + 2;
-        end if;
-      else
+  function strip_create_header_lines(a_source dbms_preprocessor.source_lines_t)
+    return dbms_preprocessor.source_lines_t
+  is
+    l_result      dbms_preprocessor.source_lines_t := a_source;
+    l_rebased     dbms_preprocessor.source_lines_t;
+    l_create_line pls_integer;
+    l_header_line pls_integer;
+    l_header_pos  pls_integer := 0;
+  begin
+    if l_result.count = 0 then
+      return l_result;
+    end if;
 
-        if l_comment_start > 0 and (l_comment_start < l_ml_comment_start or l_ml_comment_start = 0)
-           and (l_comment_start < l_text_start or l_text_start = 0) and (l_comment_start < l_escaped_text_start or l_escaped_text_start = 0)
-        then
-          l_end := instr(a_source,chr(10),l_comment_start+2);
-          if l_end > 0 then
-            l_end := l_end + 1;
-          end if;
-        elsif l_text_start > 0 and (l_text_start < l_ml_comment_start or l_ml_comment_start = 0)
-              and (l_text_start < l_comment_start or l_comment_start = 0) and (l_text_start < l_escaped_text_start or l_escaped_text_start = 0)
-        then
-          l_end := instr(a_source,q'[']',l_text_start+1);
-
-          --skip double quotes while searching for end of quoted text
-          while l_end > 0 and l_end = instr(a_source,q'['']',l_text_start+1) loop
-            l_end := instr(a_source,q'[']',l_end+1);
-          end loop;
-          if l_end > 0 then
-            l_end := l_end + 1;
-          end if;
+    -- remove comment lines that contain "-- create or replace" and find first CREATE
+    for i in 1..l_result.count loop
+      l_result(i) := regexp_replace(l_result(i), '^.*[-]{2,}\s*create(\s+or\s+replace).*$', null, 1, 1, 'i');
+      if l_create_line is null and regexp_like(l_result(i), '(^|[[:space:]])create([[:space:]]|$)', 'i') then
+        l_create_line := i;
+      end if;
+    end loop;
 
-        elsif l_escaped_text_start > 0 and (l_escaped_text_start < l_ml_comment_start or l_ml_comment_start = 0)
-              and (l_escaped_text_start < l_comment_start or l_comment_start = 0) and (l_escaped_text_start < l_text_start or l_text_start = 0)
-        then
-          --translate char "[" from the start of quoted text  "q'[someting]'" into "]"
-          l_escaped_text_end_char := translate(substr(a_source, l_escaped_text_start + 2, 1),gc_open_chars,gc_close_chars);
-          l_end := instr(a_source,l_escaped_text_end_char||'''',l_escaped_text_start + 3 );
-          if l_end > 0 then
-            l_end := l_end + 2;
-          end if;
+    -- find first occurrence of object keyword after CREATE (may be on later line)
+    if l_create_line is not null then
+      for i in l_create_line..l_result.count loop
+        l_header_pos := regexp_instr(
+          l_result(i),
+          '(^|[[:space:]])(package|type|procedure|function)([[:space:]]|$)',
+          1, 1, 0, 'i', 2
+        );
+        if l_header_pos > 0 then
+          l_header_line := i;
+          exit;
         end if;
+      end loop;
 
-        if l_end = 0 then
-          append_to_clob(l_result, substr(a_source, l_offset, l_length-l_offset));
-        else
-          append_to_clob(l_result, substr(a_source, l_offset, l_end-l_offset));
-        end if;
-      end if;
-      l_offset := l_end;
-      if l_offset >= l_ml_comment_start then
-        l_ml_comment_start := instr(a_source,'/*',l_offset);
-      end if;
-      if l_offset >= l_comment_start then
-        l_comment_start := instr(a_source,'--',l_offset);
-      end if;
-      if l_offset >= l_text_start then
-        l_text_start := instr(a_source,'''',l_offset);
-      end if;
-      if l_offset >= l_escaped_text_start then
-        l_escaped_text_start := instr(a_source,q'[q']',l_offset);
+      if l_header_line is not null then
+        -- keep from keyword onward on the header line
+        l_result(l_header_line) := substr(l_result(l_header_line), l_header_pos);
+        -- remove "OWNER." from create or replace statement.
+        -- Owner is not supported along with AUTHID - see issue https://github.com/utPLSQL/utPLSQL/issues/1088
+        l_result(l_header_line) := regexp_replace(
+          l_result(l_header_line),
+          '^(package|type|procedure|function)\s+("?[[:alpha:]][[:alnum:]$#_]*"?\.)(.*)',
+          '\1 \3', 1, 1, 'ni'
+        );
+
+        -- rebase so header line becomes line 1 (matches preprocessor expectations)
+        for i in l_header_line .. l_result.count loop
+          l_rebased(i - l_header_line + 1) := l_result(i);
+        end loop;
+        return l_rebased;
       end if;
-    end loop;
-    append_to_clob(l_result, substr(a_source, l_end));
+    end if;
+
     return l_result;
-  end;
+  end strip_create_header_lines;
 
   function get_child_reporters(a_for_reporters ut_reporters_info := null) return ut_reporters_info is
     l_for_reporters ut_reporters_info := a_for_reporters;

diff --git a/source/core/ut_utils.pks b/source/core/ut_utils.pks
@@ -406,9 +406,14 @@ create or replace package ut_utils authid definer is
   * Replaces multi-line comments in given source-code with empty lines
   */
   function replace_multiline_comments(a_source dbms_preprocessor.source_lines_t)
-  return dbms_preprocessor.source_lines_t;
-
-  function replace_multiline_comments(a_source clob) return clob;
+    return dbms_preprocessor.source_lines_t;
+
+   /**
+   * Strips the CREATE header (possibly split across lines) so source starts at
+   *   package/type/procedure/function keyword, preserving line numbers.
+   */
+  function strip_create_header_lines(a_source dbms_preprocessor.source_lines_t)
+    return dbms_preprocessor.source_lines_t;
 
    /**
    * Returns list of sub-type reporters for given list of super-type reporters