API tools faq
paste
Advertisement
elvidi

CLOB Comparison Line by Line in PL/SQL

elvidi
Apr 6th, 2011
738
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 29.47 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE PACKAGE clob_compare AS
  2.   /*=========================================================================================================
  3.     NAME: CLOB_COMPARE
  4.     PURPOSE: Provide the interface to compare two text files (clob format)
  5.              Line by Line
  6.     DESCRIPTION: I wrote this package for fun in order to be able to compare with PL/SQL code
  7.                  the changes between to packages saved as CLOBs. But it can be used to compare
  8.                  any two CLOBs where the only restriction is the maximum line length (4000).
  9.                  It is based on the naive Longest Common Subsequence algorithm described here:
  10.                    
  11.                      http://en.wikipedia.org/wiki/Longest_common_subsequence_problem
  12.                      http://en.wikipedia.org/wiki/Diff
  13.                    
  14.                  The code optimizations of Reducing the problem set and Reducing the comparison
  15.                  time are included.
  16.  
  17.     REVISIONS:
  18.     Version    Date        Author           Description
  19.     ---------  ----------  ---------------  ------------------------------------
  20.     1.0        2011-04-05  Manuel Vidigal   First Version.
  21.    
  22.  
  23.    
  24.     Copyright © 2011 to Manuel Vidigal
  25.     All Rights Reserved
  26.    
  27.     Redistribution and use in source and binary forms, with or without modification, are permitted
  28.     provided that the following conditions are met:
  29.       • Redistributions of source code must retain the above copyright notice, this list of
  30.         conditions and the following disclaimer.
  31.       • Redistributions in binary form must reproduce the above copyright notice, this list of
  32.         conditions and the following disclaimer in the documentation and/or other materials provided
  33.         with the distribution.
  34.       • Neither the name of the copyright owners nor the names of its contributors may be used to
  35.         endorse or promote products derived from this software without specific prior written permission.
  36.  
  37.     THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS
  38.     OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
  39.     AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
  40.     CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
  41.     DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  42.     LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
  43.     WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY
  44.     WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  45.  
  46.   ===========================================================================================================*/
  47.   ------------------
  48.   -- Global Types --
  49.   ------------------
  50.   TYPE t_number_array IS TABLE OF NUMBER;
  51.   TYPE t_bidimensional_number_array IS TABLE OF t_number_array;
  52.   -- =================================================================================
  53.   --  NAME:    COMPARE_LINE_BY_LINE
  54.   --  PURPOSE: Function that retrieves two CLOBs and outputs an array with the
  55.   --           differences between them (line by line)
  56.   -- =================================================================================
  57.   FUNCTION compare_line_by_line(old_file_i IN CLOB,
  58.                                 new_file_i IN CLOB) RETURN t_differences_array;
  59.   -- =================================================================================
  60.   --  NAME:    COMPARE_LINE_BY_LINE_REFCURSOR
  61.   --  PURPOSE: Function that retrieves two CLOBs and outputs a SYS_REFCURSOR with the
  62.   --           differences between them (line by line)
  63.   -- =================================================================================
  64.   FUNCTION compare_line_by_line_refcursor(old_file_i IN CLOB,
  65.                                           new_file_i IN CLOB) RETURN SYS_REFCURSOR;
  66. END clob_compare;
  67. /
  68. CREATE OR REPLACE PACKAGE BODY clob_compare AS
  69.   -- =================================================================================
  70.   --  NAME:    READ_CLOB_LINE
  71.   --  PURPOSE: Function that reads a CLOB line starting from start_position_io
  72.   --           until the end of line (chr(10))
  73.   --           If the line is bigger than 4000 Character Raises Error
  74.   -- =================================================================================
  75.   FUNCTION read_clob_line(clob_i            IN CLOB,
  76.                           start_position_io IN OUT NUMBER) RETURN VARCHAR2 IS
  77.     -- Local Variables
  78.     l_line         VARCHAR2(4000);
  79.     l_end_position NUMBER;
  80.     l_file_length  NUMBER;
  81.   BEGIN
  82.     ---------------------
  83.     -- Get CLOB length --
  84.     ---------------------
  85.     l_file_length := sys.DBMS_LOB.getlength(clob_i);
  86.     ----------------------------------------------------
  87.     -- Get the CLOB end position for the current line --
  88.     ----------------------------------------------------
  89.     l_end_position := DBMS_LOB.INSTR(lob_loc => clob_i, pattern => CHR(10), offset => start_position_io);
  90.     -------------------------------------
  91.     -- Check if line size is supported --
  92.     -------------------------------------
  93.     IF l_end_position - start_position_io > 4000 THEN
  94.       raise_application_error(-20000, 'The Maximum supported line size for the input files is 4000.');
  95.     END IF;
  96.     -------------------------------
  97.     -- If it's not the last line --
  98.     -------------------------------
  99.     IF l_end_position > 0 THEN
  100.       -------------------
  101.       -- Retrieve Line --
  102.       -------------------
  103.       l_line := RTRIM(DBMS_LOB.SUBSTR(lob_loc => clob_i,
  104.                                       amount  => LEAST(l_end_position - start_position_io, 4000),
  105.                                       offset  => start_position_io),
  106.                       CHR(13) || CHR(10));
  107.       ---------------------------------
  108.       -- Mark the new start position --
  109.       ---------------------------------
  110.       start_position_io := l_end_position + 1;
  111.       ------------------
  112.       -- If last line --
  113.       ------------------
  114.     ELSE
  115.       -------------------
  116.       -- Retrieve Line --
  117.       -------------------
  118.       l_line := DBMS_LOB.SUBSTR(lob_loc => clob_i,
  119.                                 amount  => l_file_length - start_position_io + 1,
  120.                                 offset  => start_position_io);
  121.       --------------------------------------
  122.       -- Mark start position as finnished --
  123.       --------------------------------------
  124.       start_position_io := 0;
  125.     END IF;
  126.     --
  127.     RETURN l_line;
  128.   END read_clob_line;
  129.   -- =================================================================================
  130.   --  NAME:    CLOB_TO_ARRAY
  131.   --  PURPOSE: Function that returns an array with all CLOB lines
  132.   -- =================================================================================
  133.   FUNCTION clob_to_array(clob_i IN CLOB) RETURN t_varchar2_array IS
  134.     -- Local Variables
  135.     l_array          t_varchar2_array := t_varchar2_array();
  136.     l_start_position NUMBER := 1;
  137.   BEGIN
  138.     --------------------------------
  139.     -- While there is a next line --
  140.     --------------------------------
  141.     WHILE l_start_position != 0
  142.     LOOP
  143.       -----------------------------------
  144.       -- Populate array with clob line --
  145.       -----------------------------------
  146.       l_array.extend;
  147.       l_array(l_array.COUNT) := read_clob_line(clob_i, l_start_position);
  148.     END LOOP;
  149.     --
  150.     RETURN l_array;
  151.   END clob_to_array;
  152.   -- =================================================================================
  153.   --  NAME:    GET_START_END_LINES
  154.   --  PURPOSE: Procedure that retrieves the start and end line where the arrays are
  155.   --           different in order to reduce the problem set
  156.   -- =================================================================================
  157.   PROCEDURE get_start_end_lines(old_array_i          IN t_varchar2_array,
  158.                                 new_array_i          IN t_varchar2_array,
  159.                                 start_line_o         OUT NUMBER,
  160.                                 end_line_old_o       OUT NUMBER,
  161.                                 end_line_new_o       OUT NUMBER,
  162.                                 differences_array_io IN OUT NOCOPY t_differences_array) IS
  163.   BEGIN
  164.     --------------------------
  165.     -- Initialize variables --
  166.     --------------------------
  167.     start_line_o   := 1;
  168.     end_line_old_o := old_array_i.COUNT;
  169.     end_line_new_o := new_array_i.COUNT;
  170.     -------------------------------------------------------
  171.     -- Get first line where arrays are different         --
  172.     -- Populate Diff Array with the first matching lines --
  173.     -------------------------------------------------------
  174.     WHILE start_line_o <= end_line_old_o
  175.           AND start_line_o <= end_line_new_o
  176.           AND (old_array_i(start_line_o) = new_array_i(start_line_o) OR
  177.           (old_array_i(start_line_o) IS NULL AND new_array_i(start_line_o) IS NULL))
  178.     LOOP
  179.       differences_array_io.extend;
  180.       differences_array_io(differences_array_io.LAST) := t_differences(start_line_o,
  181.                                                                        old_array_i(start_line_o),
  182.                                                                        '========',
  183.                                                                        start_line_o,
  184.                                                                        new_array_i(start_line_o));
  185.       start_line_o := start_line_o + 1;
  186.     END LOOP;
  187.     ------------------------------------------------------
  188.     -- Get the end lines untillthe arrays are different --
  189.     ------------------------------------------------------
  190.     WHILE start_line_o <= end_line_old_o
  191.           AND start_line_o <= end_line_new_o
  192.           AND (old_array_i(end_line_old_o) = new_array_i(end_line_new_o) OR
  193.           (old_array_i(end_line_old_o) IS NULL AND new_array_i(end_line_new_o) IS NULL))
  194.     LOOP
  195.       end_line_old_o := end_line_old_o - 1;
  196.       end_line_new_o := end_line_new_o - 1;
  197.     END LOOP;
  198.   END get_start_end_lines;
  199.   -- =================================================================================
  200.   --  NAME:    INITIALIZE_MATRIX
  201.   --  PURPOSE: Procedure that inicializes the Longest Common Subsequence (LCS) Matrix
  202.   -- =================================================================================
  203.   PROCEDURE initialize_matrix(matrix_i       IN OUT NOCOPY t_bidimensional_number_array,
  204.                               start_line_i   IN NUMBER,
  205.                               end_line_old_i IN NUMBER,
  206.                               end_line_new_i IN NUMBER) IS
  207.   BEGIN
  208.     ----------------------------------------------------------------------
  209.     -- Since LCS Matrix starts with zero and Oracle arrays start with 1 --
  210.     -- we need to loop through all different old lines plus 1           --
  211.     ----------------------------------------------------------------------
  212.     FOR i IN 1 .. (end_line_old_i - start_line_i + 1) + 1
  213.     LOOP
  214.       ----------------------------------
  215.       -- Extend two-dimensional array --
  216.       ----------------------------------
  217.       matrix_i.extend;
  218.       ---------------------------------------
  219.       -- initialize second dimention array --
  220.       ---------------------------------------
  221.       matrix_i(i) := t_number_array();
  222.       ----------------------------------------------------------------------
  223.       -- Since LCS Matrix starts with zero and Oracle arrays start with 1 --
  224.       -- we need to loop through all different new lines plus 1           --
  225.       ----------------------------------------------------------------------
  226.       FOR j IN 1 .. (end_line_new_i - start_line_i + 1) + 1
  227.       LOOP
  228.         -----------------------------------------------------------------------
  229.         -- Extend second dimension array (this extends first dimension only) --
  230.         -----------------------------------------------------------------------
  231.         matrix_i(i).extend;
  232.         ----------------------------------------------
  233.         -- If field is in first row or first column --
  234.         ----------------------------------------------
  235.         IF i = 1
  236.            OR j = 1 THEN
  237.           ------------------------
  238.           -- Populate with zero --
  239.           ------------------------
  240.           matrix_i(i)(j) := 0;
  241.         END IF;
  242.       END LOOP;
  243.     END LOOP;
  244.   END initialize_matrix;
  245.   -- =================================================================================
  246.   --  NAME:    POPULATE_MATRIX
  247.   --  PURPOSE: Procedure that populates LCS Matrix
  248.   -- =================================================================================
  249.   PROCEDURE populate_matrix(matrix_i       IN OUT NOCOPY t_bidimensional_number_array,
  250.                             old_array_i    IN t_varchar2_array,
  251.                             new_array_i    IN t_varchar2_array,
  252.                             start_line_i   IN NUMBER,
  253.                             end_line_old_i IN NUMBER,
  254.                             end_line_new_i IN NUMBER) IS
  255.   BEGIN
  256.     ----------------------------------------------------
  257.     -- Loop through all Matrix elements               --
  258.     -- The loop starts at 2 since the first column is --
  259.     -- already populated                              --
  260.     ----------------------------------------------------
  261.     FOR i IN 2 .. (end_line_old_i - start_line_i + 1) + 1
  262.     LOOP
  263.       FOR j IN 2 .. (end_line_new_i - start_line_i + 1) + 1
  264.       LOOP
  265.         -----------------------------------------------
  266.         -- Populate LCS array based on LCS algorithm --
  267.         -----------------------------------------------
  268.         IF old_array_i(i - 1) = new_array_i(j - 1) THEN
  269.           matrix_i(i)(j) := matrix_i(i - 1) (j - 1) + 1;
  270.         ELSE
  271.           matrix_i(i)(j) := GREATEST(matrix_i(i) (j - 1), matrix_i(i - 1) (j));
  272.         END IF;
  273.       END LOOP;
  274.     END LOOP;
  275.   END populate_matrix;
  276.   -- =================================================================================
  277.   --  NAME:    GET_DIFFERENCES
  278.   --  PURPOSE: Procedure that populates the Diff Array based on the LCS Matrix
  279.   -- =================================================================================
  280.   PROCEDURE get_differences(matrix_i                   IN t_bidimensional_number_array,
  281.                             old_array_i                IN t_varchar2_array,
  282.                             new_array_i                IN t_varchar2_array,
  283.                             i                          IN NUMBER,
  284.                             j                          IN NUMBER,
  285.                             start_line_i               IN NUMBER,
  286.                             end_line_old_i             IN NUMBER,
  287.                             end_line_new_i             IN NUMBER,
  288.                             last_populated_old_line_io IN OUT NUMBER,
  289.                             last_populated_new_line_io IN OUT NUMBER,
  290.                             differences_array_io       IN OUT NOCOPY t_differences_array) IS
  291.   BEGIN
  292.     -------------------------------------------------------------
  293.     -- If iteraters are greater than zero and the arrays match --
  294.     -------------------------------------------------------------
  295.     IF i > 0
  296.        AND j > 0
  297.        AND (old_array_i(i + start_line_i - 1) = new_array_i(j + start_line_i - 1) OR
  298.        (old_array_i(i + start_line_i - 1) IS NULL AND new_array_i(j + start_line_i - 1) IS NULL)) THEN
  299.       ------------------------------------------------------
  300.       -- Call get_differences for previous Matrix element --
  301.       ------------------------------------------------------
  302.       get_differences(matrix_i                   => matrix_i,
  303.                       old_array_i                => old_array_i,
  304.                       new_array_i                => new_array_i,
  305.                       i                          => i - 1,
  306.                       j                          => j - 1,
  307.                       start_line_i               => start_line_i,
  308.                       end_line_old_i             => end_line_old_i,
  309.                       end_line_new_i             => end_line_new_i,
  310.                       last_populated_old_line_io => last_populated_old_line_io,
  311.                       last_populated_new_line_io => last_populated_new_line_io,
  312.                       differences_array_io       => differences_array_io);
  313.       -------------------------
  314.       -- Populate Diff Array --
  315.       -------------------------
  316.       differences_array_io.extend;
  317.       differences_array_io(differences_array_io.LAST) := t_differences(i + start_line_i - 1,
  318.                                                                        old_array_i(i + start_line_i - 1),
  319.                                                                        '========',
  320.                                                                        j + start_line_i - 1,
  321.                                                                        new_array_i(j + start_line_i - 1));
  322.       -----------------------------------------
  323.       -- Reset last populated line variables --
  324.       -----------------------------------------
  325.       last_populated_old_line_io := NULL;
  326.       last_populated_new_line_io := NULL;
  327.     ELSE
  328.       IF j > 0
  329.          AND (i = 0 OR matrix_i(i + 1) (j) >= matrix_i(i) (j + 1)) THEN
  330.         ------------------------------------------------------
  331.         -- Call get_differences for previous Matrix element --
  332.         ------------------------------------------------------
  333.         get_differences(matrix_i                   => matrix_i,
  334.                         old_array_i                => old_array_i,
  335.                         new_array_i                => new_array_i,
  336.                         i                          => i,
  337.                         j                          => j - 1,
  338.                         start_line_i               => start_line_i,
  339.                         end_line_old_i             => end_line_old_i,
  340.                         end_line_new_i             => end_line_new_i,
  341.                         last_populated_old_line_io => last_populated_old_line_io,
  342.                         last_populated_new_line_io => last_populated_new_line_io,
  343.                         differences_array_io       => differences_array_io);
  344.         --
  345.         IF last_populated_old_line_io IS NOT NULL THEN
  346.           differences_array_io(last_populated_old_line_io).new_line_number := j + start_line_i - 1;
  347.           differences_array_io(last_populated_old_line_io).new_file := new_array_i(j + start_line_i - 1);
  348.           last_populated_old_line_io := last_populated_old_line_io + 1;
  349.         ELSE
  350.           -------------------------
  351.           -- Populate Diff Array --
  352.           -------------------------
  353.           differences_array_io.extend;
  354.           differences_array_io(differences_array_io.LAST) := t_differences(NULL,
  355.                                                                            NULL,
  356.                                                                            '<<<<>>>>',
  357.                                                                            j + start_line_i - 1,
  358.                                                                            new_array_i(j + start_line_i - 1));
  359.           IF last_populated_new_line_io IS NULL THEN
  360.             last_populated_new_line_io := differences_array_io.LAST;
  361.           END IF;
  362.         END IF;
  363.       ELSIF i > 0
  364.             AND (j = 0 OR matrix_i(i + 1) (j) < matrix_i(i) (j + 1)) THEN
  365.         ------------------------------------------------------
  366.         -- Call get_differences for previous Matrix element --
  367.         ------------------------------------------------------
  368.         get_differences(matrix_i                   => matrix_i,
  369.                         old_array_i                => old_array_i,
  370.                         new_array_i                => new_array_i,
  371.                         i                          => i - 1,
  372.                         j                          => j,
  373.                         start_line_i               => start_line_i,
  374.                         end_line_old_i             => end_line_old_i,
  375.                         end_line_new_i             => end_line_new_i,
  376.                         last_populated_old_line_io => last_populated_old_line_io,
  377.                         last_populated_new_line_io => last_populated_new_line_io,
  378.                         differences_array_io       => differences_array_io);
  379.         --
  380.         IF last_populated_new_line_io IS NOT NULL THEN
  381.           differences_array_io(last_populated_new_line_io).old_line_number := i + start_line_i - 1;
  382.           differences_array_io(last_populated_new_line_io).old_file := old_array_i(i + start_line_i - 1);
  383.           last_populated_new_line_io := last_populated_new_line_io + 1;
  384.         ELSE
  385.           -------------------------
  386.           -- Populate Diff Array --
  387.           -------------------------
  388.           differences_array_io.extend;
  389.           differences_array_io(differences_array_io.LAST) := t_differences(i + start_line_i - 1,
  390.                                                                            old_array_i(i + start_line_i - 1),
  391.                                                                            '<<<<>>>>',
  392.                                                                            NULL,
  393.                                                                            NULL);
  394.           ----------------------------------------------------------
  395.           -- If the last populated parameter is not yet populated --
  396.           ----------------------------------------------------------
  397.           IF last_populated_old_line_io IS NULL THEN
  398.             --------------------------------------------
  399.             -- Save the next old line to be populated --
  400.             --------------------------------------------
  401.             last_populated_old_line_io := differences_array_io.LAST;
  402.           END IF;
  403.         END IF;
  404.       END IF;
  405.     END IF;
  406.   END get_differences;
  407.   -- =================================================================================
  408.   --  NAME:    POPULATE_DIFF_LAST_LINES
  409.   --  PURPOSE: Procedure that populates the Diff Array with the last matching lines
  410.   -- =================================================================================
  411.   PROCEDURE populate_diff_last_lines(old_array_i          IN t_varchar2_array,
  412.                                      new_array_i          IN t_varchar2_array,
  413.                                      end_line_old_i       IN NUMBER,
  414.                                      end_line_new_i       IN NUMBER,
  415.                                      differences_array_io IN OUT NOCOPY t_differences_array) IS
  416.     -- Local Variables
  417.     l_end_line_old NUMBER := end_line_old_i + 1;
  418.     l_end_line_new NUMBER := end_line_new_i + 1;
  419.   BEGIN
  420.     -------------------------------------------
  421.     -- Loop through all last matching lines  --
  422.     -------------------------------------------
  423.     FOR i IN 1 .. old_array_i.COUNT - end_line_old_i
  424.     LOOP
  425.       --------------------------
  426.       -- Populate Diff Array  --
  427.       --------------------------
  428.       differences_array_io.extend;
  429.       differences_array_io(differences_array_io.LAST) := t_differences(l_end_line_old,
  430.                                                                        old_array_i(l_end_line_old),
  431.                                                                        '========',
  432.                                                                        l_end_line_new,
  433.                                                                        new_array_i(l_end_line_new));
  434.       --------------------------
  435.       -- Increment Variables  --
  436.       --------------------------
  437.       l_end_line_old := l_end_line_old + 1;
  438.       l_end_line_new := l_end_line_new + 1;
  439.     END LOOP;
  440.   END populate_diff_last_lines;
  441.   -- =================================================================================
  442.   --  NAME:    COMPARE_LINE_BY_LINE
  443.   --  PURPOSE: Function that retrieves two CLOBs and outputs an array with the
  444.   --           differences between them (line by line)
  445.   -- =================================================================================
  446.   FUNCTION compare_line_by_line(old_file_i IN CLOB,
  447.                                 new_file_i IN CLOB) RETURN t_differences_array IS
  448.     -- Local Variables
  449.     l_old_file_array          t_varchar2_array;
  450.     l_new_file_array          t_varchar2_array;
  451.     l_old_file_array_hashed   t_varchar2_array;
  452.     l_new_file_array_hashed   t_varchar2_array;
  453.     l_start_line              NUMBER;
  454.     l_end_line_old            NUMBER;
  455.     l_end_line_new            NUMBER;
  456.     l_last_populated_old_line NUMBER;
  457.     l_last_populated_new_line NUMBER;
  458.     l_matrix                  t_bidimensional_number_array := NEW t_bidimensional_number_array();
  459.     l_differences_array       t_differences_array := NEW t_differences_array();
  460.   BEGIN
  461.     ---------------------------------
  462.     -- Transform Clobs into Arrays --
  463.     ---------------------------------
  464.     l_old_file_array := clob_to_array(old_file_i);
  465.     l_new_file_array := clob_to_array(new_file_i);
  466.     -----------------
  467.     -- Hash Arrays --
  468.     -----------------
  469.     SELECT DBMS_UTILITY.get_hash_value(column_value, 2, 1048576) BULK COLLECT
  470.       INTO l_old_file_array_hashed
  471.       FROM TABLE(l_old_file_array);
  472.     -----------------
  473.     -- Hash Arrays --
  474.     -----------------
  475.     SELECT DBMS_UTILITY.get_hash_value(column_value, 2, 1048576) BULK COLLECT
  476.       INTO l_new_file_array_hashed
  477.       FROM TABLE(l_new_file_array);
  478.     ------------------------------------------------------
  479.     -- Get Start and End Line of Differences            --
  480.     -- Populate de Diff Array with Start Matching Lines --
  481.     ------------------------------------------------------
  482.     get_start_end_lines(old_array_i          => l_old_file_array,
  483.                         new_array_i          => l_new_file_array,
  484.                         start_line_o         => l_start_line,
  485.                         end_line_old_o       => l_end_line_old,
  486.                         end_line_new_o       => l_end_line_new,
  487.                         differences_array_io => l_differences_array);
  488.     ---------------------------
  489.     -- Inicialize LCS Matrix --
  490.     ---------------------------
  491.     initialize_matrix(matrix_i       => l_matrix,
  492.                       start_line_i   => l_start_line,
  493.                       end_line_old_i => l_end_line_old,
  494.                       end_line_new_i => l_end_line_new);
  495.     -------------------------
  496.     -- Populate LCS Matrix --
  497.     -------------------------
  498.     populate_matrix(matrix_i       => l_matrix,
  499.                     old_array_i    => l_old_file_array_hashed,
  500.                     new_array_i    => l_new_file_array_hashed,
  501.                     start_line_i   => l_start_line,
  502.                     end_line_old_i => l_end_line_old,
  503.                     end_line_new_i => l_end_line_new);
  504.     ---------------------------------------------
  505.     -- Populate Diff Array Based on LCS Matrix --
  506.     ---------------------------------------------
  507.     get_differences(matrix_i                   => l_matrix,
  508.                     old_array_i                => l_old_file_array,
  509.                     new_array_i                => l_new_file_array,
  510.                     i                          => l_end_line_old - l_start_line + 1,
  511.                     j                          => l_end_line_new - l_start_line + 1,
  512.                     start_line_i               => l_start_line,
  513.                     end_line_old_i             => l_end_line_old,
  514.                     end_line_new_i             => l_end_line_new,
  515.                     last_populated_old_line_io => l_last_populated_old_line,
  516.                     last_populated_new_line_io => l_last_populated_new_line,
  517.                     differences_array_io       => l_differences_array);
  518.     --------------------------------------------
  519.     -- Populate Diff Array End Matching Lines --
  520.     --------------------------------------------
  521.     populate_diff_last_lines(old_array_i          => l_old_file_array,
  522.                              new_array_i          => l_new_file_array,
  523.                              end_line_old_i       => l_end_line_old,
  524.                              end_line_new_i       => l_end_line_new,
  525.                              differences_array_io => l_differences_array);
  526.     --
  527.     RETURN l_differences_array;
  528.   END compare_line_by_line;
  529.   -- =================================================================================
  530.   --  NAME:    COMPARE_LINE_BY_LINE_REFCURSOR
  531.   --  PURPOSE: Function that retrieves two CLOBs and outputs a SYS_REFCURSOR with the
  532.   --           differences between them (line by line)
  533.   -- =================================================================================
  534.   FUNCTION compare_line_by_line_refcursor(old_file_i IN CLOB,
  535.                                           new_file_i IN CLOB) RETURN SYS_REFCURSOR IS
  536.     -- Local Variables
  537.     l_cursor            SYS_REFCURSOR;
  538.     l_differences_array t_differences_array;
  539.   BEGIN
  540.     --------------------------------
  541.     -- Get Differences into Array --
  542.     --------------------------------
  543.     l_differences_array := compare_line_by_line(old_file_i => old_file_i, new_file_i => new_file_i);
  544.     -------------------------------------
  545.     -- Open Cursor based on Diff Array --
  546.     -------------------------------------
  547.     OPEN l_cursor FOR
  548.       SELECT *
  549.         FROM TABLE(l_differences_array);
  550.     --
  551.     RETURN l_cursor;
  552.   END compare_line_by_line_refcursor;
  553.  
  554. END clob_compare;
  555. /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!