create or replace FUNCTION lcs(      
  in_str1 IN VARCHAR2,      
  in_str2 IN VARCHAR2      
) RETURN VARCHAR2      
IS      
  TYPE numbers_array IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;      
  TYPE Array2D IS TABLE OF numbers_array INDEX BY PLS_INTEGER;      
  c_l1                  SIMPLE_INTEGER := 0;      
  c_l2                  SIMPLE_INTEGER := 0;      
  p_matrix              Array2D;      
  p_c1                  NCHAR(1);      
  p_c2                  NCHAR(1);      
  endingIndex           SIMPLE_INTEGER := 0;      
  maxlen                SIMPLE_INTEGER := 0; --lcs    
  l_check               SIMPLE_INTEGER := 0; --check if there are any matching characters between the 2 strings    
BEGIN      
          
    IF in_str1 IS NULL OR in_str2 IS NULL THEN      
        RETURN NULL;      
    ELSIF c_l1 > c_l2 THEN      
        RETURN lcs( in_str2, in_str1 );      
    END IF;    
        
    --check if there are any matching characters; if none found, RETURN NULL    
    --@todo: might consider removing the whitespaces before making the comparison    
    SELECT    
        COUNT(1)    
    INTO    
        l_check    
    FROM    
    (    
        SELECT in_str1 initial_string, TRANSLATE ( in_str1, in_str2, '#' ) output_string FROM dual    
    )    
    WHERE    
        initial_String = output_string    
    ;    
    IF l_check = 1    
    THEN    
        RETURN NULL;    
    END IF;    
        
    c_l1 := LENGTH(in_str1);      
    c_l2 := LENGTH(in_str2);      
          
    --initialize all cells of lookup table to 0    
    FOR i IN 1..c_l1    
    LOOP    
        FOR j IN 1..c_l2    
        LOOP    
            p_matrix(i)(j) := 0;    
        END LOOP;    
    END LOOP;    
    
    FOR i IN 1..c_l1      
    LOOP      
        --Check if the length of current position + length of remaining string <= max length found, exit    
        p_c1 := SUBSTR( in_str1, i, 1 );    
        FOR j IN 1..c_l2      
        LOOP      
            p_c2 := SUBSTR( in_str2, j, 1 );      
            --if current character of X and Y matches      
            IF p_c1 = p_c2      
            THEN      
                if p_matrix.exists ( i-1 ) and p_matrix ( i-1 ).exists ( j-1 )     
                then    
                    p_matrix ( i )( j ) := p_matrix ( i-1 )( j-1 ) + 1;     
                else     
                    p_matrix ( i )( j ) := 1;    
                end if;    
                    
                IF p_matrix(i)(j) > maxlen      
                THEN      
                    maxlen := p_matrix(i)(j);      
                    endingIndex := i;    
                END IF;    
                /*    
                IF (c_l1 - i) < maxlen    
                THEN    
                    RETURN SUBSTR(in_str1, endingIndex - maxlen + 1, maxlen);      
                END IF;    
                */    
            END IF;      
        END LOOP;      
    END LOOP;      
    
    RETURN SUBSTR(in_str1, endingIndex - maxlen + 1, maxlen);      
      
END lcs; 
 

create or replace type strings_Array as TABLE of VARCHAR2(100);

create or replace function cs(  
  in_str1 IN VARCHAR2,  
  in_str2 IN VARCHAR2      
) RETURN strings_array  
AS  
  
    v_lcs   VARCHAR2(100);  
    l_array strings_array := strings_array();  
  
BEGIN  
    --compute longest common substring  
    v_lcs := lcs(in_str1, in_str2);  
    IF v_lcs  = NULL  
    THEN  
        RETURN l_array;  
    END IF;  
      
    l_array.EXTEND;  
    l_array(1) := v_lcs;  
      
    --@TODO: divide and conquer  
      
      
      
    return l_array;  
      
END cs; 

select * from table(cs('WIKIMEDIA', 'REMEDIYA'));