DROP TABLE TEST_TABLE;
DROP TYPE tRECORDS;
DROP TYPE tRECORD;
DROP FUNCTION COMPARE_COLS;

CREATE TABLE TEST_TABLE (COLA VARCHAR2(100), COLB VARCHAR2(100));
INSERT INTO TEST_TABLE VALUES ('abc;mno;def','abc;def;xyz');
INSERT INTO TEST_TABLE VALUES ('efg','efg');
INSERT INTO TEST_TABLE VALUES ('def;abc','abc;def');
INSERT INTO TEST_TABLE VALUES (NULL, 'abc;def');
INSERT INTO TEST_TABLE VALUES (NULL, NULL);
INSERT INTO TEST_TABLE VALUES ('abc;def', NULL);
COMMIT;

CREATE TYPE tRECORD AS OBJECT (MATCHED VARCHAR2(30), MISSING_A VARCHAR2(100), MISSING_B VARCHAR2(100));
/

CREATE TYPE tRECORDS AS TABLE OF tRECORD;
/

CREATE OR REPLACE FUNCTION COMPARE_COLS(COLA IN VARCHAR2, COLB IN VARCHAR2)
RETURN tRECORDS
PIPELINED
IS
  TYPE tTYPE IS TABLE OF VARCHAR(100);
  DATA_COLA   DBMS_UTILITY.UNCL_ARRAY;
  DATA_COLB   DBMS_UTILITY.UNCL_ARRAY;
  MATCH_OUTCOME VARCHAR2(30);
  MISSING_A VARCHAR2(100);
  MISSING_B VARCHAR2(100);
  LEN_A BINARY_INTEGER;
  LEN_B BINARY_INTEGER;
  COL_SEP CHAR(1);
BEGIN

  CASE
    WHEN COLA IS NULL AND COLB IS NULL THEN 
      MATCH_OUTCOME := 'MATCHED';
    WHEN COLA IS NULL AND COLB IS NOT NULL THEN
      MATCH_OUTCOME := 'NOT MATCHING';
      MISSING_A := COLB;
    WHEN COLB IS NULL AND COLA IS NOT NULL THEN
      MATCH_OUTCOME := 'NOT MATCHING';
      MISSING_B := COLA;
    WHEN COLA = COLB THEN 
      MATCH_OUTCOME := 'MATCHED';
    WHEN COLB <> COLA THEN
      DBMS_UTILITY.COMMA_TO_TABLE(REPLACE(COLA, ';', ','), LEN_A, DATA_COLA);
      DBMS_UTILITY.COMMA_TO_TABLE(REPLACE(COLB, ';', ','), LEN_B, DATA_COLB);
      
      COL_SEP := NULL;
      FOR I IN 1..DATA_COLA.COUNT LOOP
        IF INSTR(COLB, DATA_COLA(I)) = 0 THEN
          MISSING_B := MISSING_B || COL_SEP || DATA_COLA(I);
          COL_SEP := ';';
        END IF;
      END LOOP;
      
      COL_SEP := NULL;
      FOR I IN 1..DATA_COLB.COUNT LOOP
        IF INSTR(COLA, DATA_COLB(I)) = 0 THEN
          MISSING_A := MISSING_A || COL_SEP || DATA_COLB(I);
          COL_SEP := ';';
        END IF;
      END LOOP;
      
      IF MISSING_A IS NOT NULL OR MISSING_B IS NOT NULL
        THEN MATCH_OUTCOME := 'NOT MATCHING';
        ELSE MATCH_OUTCOME := 'MATCHED';
      END IF;
    ELSE
      RAISE_APPLICATION_ERROR(-20999, 'WTF');
  END CASE;
  
  PIPE ROW(tRECORD(MATCH_OUTCOME, MISSING_A, MISSING_B));

  RETURN;
END;
/

SET LINESIZE 140;
SET NULL '(null)';
COLUMN COLA FORMAT A30;
COLUMN COLB FORMAT A30;
COLUMN MATCHED FORMAT A20;
COLUMN MISSING_A FORMAT A20;
COLUMN MISSING_B FORMAT A20;

SELECT *
FROM TEST_TABLE, TABLE(COMPARE_COLS(COLA, COLB));