GET_MAPPING_DQL based on columns

PROCEDURE PROC_GET_MAPPING_DQL(IN_USER VARCHAR2,IN_TEST1_TAB VARCHAR2,IN_TEST2_TAB VARCHAR2,IN_BUSI_DT DATE)
  IS
    V_IN_TEST2_TAB     VARCHAR2(32):=UPPER(IN_TEST2_TAB);
    V_IN_TEST1_TAB    VARCHAR2(32):=UPPER(IN_TEST1_TAB);
    V_PK_COL_JOIN    VARCHAR2(1024);
    V_PK_COL         VARCHAR2(512);
    --V_IN_EAST_DATE   VARCHAR2(64):=TO_CHAR(IN_BUSI_DT,'YYYY-MM-DD');
    V_IN_TEST_DATE    VARCHAR2(64):=TO_CHAR(IN_BUSI_DT,'YYYY-MM-DD');
    V_NULL_TYPE      VARCHAR2(64);
    V_MAP_COLS       CLOB;
    --V_SELECT_COLS    CLOB;
    V_PREDICATE      CLOB;
    V_MAP_DQL        CLOB;
    V_TEST_OVER       CLOB;
    V_EAST_OVER      CLOB;
    V_MORE_COLS      CLOB;

  BEGIN
    /*SELECT A.COLUMN_NAME,A.DATA_TYPE FROM USER_TAB_COLUMNS A,USER_TAB_COLUMNS B
              WHERE A.COLUMN_NAME=B.COLUMN_NAME
                AND A.TABLE_NAME=V_IN_TEST2_TAB
                AND B.TABLE_NAME=V_IN_TEST1_TAB*/--这是共同字段,按理应该包含所有字段

    FOR I IN (SELECT A.COLUMN_NAME,DATA_TYPE,--SUBSTR(REPLACE(B.COMMENTS,CHR(10),''),1,9) AS COMMENTS
                     case when lengthb(coalesce(substr(REPLACE(b.comments, TO_CHAR(CHR(10)),':'),1,instr(REPLACE(b.comments, TO_CHAR(CHR(10)),' '),' ')-1),b.comments,a.column_name))>30 
                     then
                      substrb(coalesce(substr(REPLACE(b.comments, TO_CHAR(CHR(10)),':'),1,instr(REPLACE(b.comments, TO_CHAR(CHR(10)),' '),' ')-1),b.comments,a.column_name),-30)
                     else
                      coalesce(substr(REPLACE(b.comments, TO_CHAR(CHR(10)),':'),1,instr(REPLACE(b.comments, TO_CHAR(CHR(10)),' '),' ')-1),b.comments,a.column_name) 
                     end as comments
                FROM ALL_TAB_COLUMNS A
                LEFT JOIN ALL_COL_COMMENTS B
                  ON A.TABLE_NAME = B.TABLE_NAME
                 AND A.COLUMN_NAME = B.COLUMN_NAME
                 AND A.OWNER = B.OWNER
               WHERE A.TABLE_NAME=V_IN_TEST1_TAB
                 AND A.OWNER = UPPER(IN_USER)
                 AND A.COLUMN_NAME NOT IN('COL1','COL2')
               ORDER BY COLUMN_ID)

    LOOP
      SELECT DECODE(I.DATA_TYPE,'DATE','DATE''9999-12-31''',0)
        INTO V_NULL_TYPE
        FROM DUAL;

      V_MAP_COLS :=V_MAP_COLS||'CASE WHEN NVL(TB_TEST1.'||I.COLUMN_NAME||','
                             ||V_NULL_TYPE||')=NVL(TEST4_I.'||I.COLUMN_NAME||','
                             ||V_NULL_TYPE||') THEN ''Y'' ELSE TB_TEST1.'
                             ||I.COLUMN_NAME||'||'' <> ''||TEST4_I.'
                             ||I.COLUMN_NAME||' END '||'"'||NVL(I.COMMENTS,I.COLUMN_NAME)||'"'||','||CHR(10)||'                            ';
      --V_SELECT_COLS:=V_SELECT_COLS||I.COLUMN_NAME||','||CHR(10);
      V_PREDICATE :=V_PREDICATE||'"'||NVL(I.COMMENTS,I.COLUMN_NAME)||'"'||'<>''Y'' OR '||CHR(10)||'                        ';

      V_MORE_COLS := V_MORE_COLS || I.COLUMN_NAME || ' AS ' ||
                     '"'||NVL(I.COMMENTS,I.COLUMN_NAME)||'",';

    END LOOP;
    V_MAP_COLS    :=RTRIM(V_MAP_COLS,','||CHR(10)||'                            ');
    --V_SELECT_COLS :=RTRIM(V_SELECT_COLS,',')||CHR(10);
    V_PREDICATE   :=RTRIM(V_PREDICATE,' OR '||CHR(10)||'                        ');
    V_MORE_COLS   :=RTRIM(V_MORE_COLS,',');

    SELECT LISTAGG('TB_TEST1.'||REGEXP_SUBSTR(V_COLUMNS,'[^,]+',1,ROWNUM)||
                   ' = TEST4_I.' ||REGEXP_SUBSTR(V_COLUMNS,'[^,]+',1,ROWNUM),
                   CHR(10)||'                             AND ')
           WITHIN GROUP(ORDER BY V_COLUMNS) AS PK_COL_LIST,

           LISTAGG('TB_TEST1.'||REGEXP_SUBSTR(V_COLUMNS,'[^,]+',1,ROWNUM)||' AS PK_'||REGEXP_SUBSTR(V_COLUMNS,'[^,]+',1,ROWNUM),',')
           WITHIN GROUP(ORDER BY V_COLUMNS) AS PK_COL
      INTO V_PK_COL_JOIN,V_PK_COL
      FROM
      (
        SELECT CONS.CONSTRAINT_NAME,CONS.TABLE_NAME,REPLACE(IND_VIEW.V_COLUMNS,'DATA_DATE,','') AS V_COLUMNS
          FROM ALL_CONSTRAINTS CONS,(SELECT TABLE_NAME,INDEX_NAME,
                                           LISTAGG(COLUMN_NAME,',')WITHIN GROUP(ORDER BY COLUMN_POSITION) AS V_COLUMNS
                                      FROM ALL_IND_COLUMNS
                                      WHERE INDEX_OWNER = 'USER4'
                                     GROUP BY INDEX_NAME,TABLE_NAME) IND_VIEW
         WHERE CONS.CONSTRAINT_NAME=IND_VIEW.INDEX_NAME
           AND CONS.TABLE_NAME=IND_VIEW.TABLE_NAME
           AND CONS.OWNER='USER4'
           AND CONS.TABLE_NAME=REPLACE(IN_TEST2_TAB,'_BAK','')
           AND CONS.CONSTRAINT_TYPE='P'
      )
      CONNECT BY LEVEL<=REGEXP_COUNT(V_COLUMNS,',')+1;

    IF V_PK_COL IS NOT NULL THEN
       V_MAP_COLS := V_PK_COL||','||V_MAP_COLS;
    END IF;

    V_MAP_DQL:='SELECT *
                  FROM
                      (
                         SELECT COUNT(*) OVER() MAPPING_COUNT,'||V_MAP_COLS||'
                           FROM '||IN_USER||'.'||V_IN_TEST1_TAB||' TB_TEST1,'||'USER4.'||V_IN_TEST2_TAB||' TEST4_I
                          WHERE '||V_PK_COL_JOIN|| 
                            ' AND TB_TEST1.BUSI_DT= DATE'''||V_IN_TEST_DATE||'''
                      )
                 WHERE ('||V_PREDICATE||');';

    V_TEST_OVER:='SELECT '||V_MORE_COLS||'
  FROM '||'USER4.'||V_IN_TEST2_TAB||' TEST4_I
 WHERE NOT EXISTS
           (SELECT 1 FROM '||IN_USER||'.'||V_IN_TEST1_TAB||' TB_TEST1
             WHERE '||V_PK_COL_JOIN||
             ' AND TB_TEST1.BUSI_DT=DATE'''||V_IN_TEST_DATE||''');';

    V_EAST_OVER:='SELECT '||V_MORE_COLS||'
  FROM '||IN_USER||'.'||V_IN_TEST1_TAB||' TB_TEST1
 WHERE NOT EXISTS
           (SELECT 1 FROM '||'USER4.'||V_IN_TEST2_TAB||' TEST4_I
             WHERE '||V_PK_COL_JOIN||')
   AND TB_TEST1.BUSI_DT=DATE'''||V_IN_TEST_DATE||''';';

    DELETE TEST_GET_MAPPING_DQL WHERE DM_TABNAME=V_IN_TEST1_TAB;
    COMMIT;

    INSERT INTO TEST_GET_MAPPING_DQL(MAPPING_DQL,PROD_MORE_DQL,DM_MORE_DQL,DM_TABNAME,PROD_TABNAME,INPUT_DT)
      VALUES (V_MAP_DQL,V_TEST_OVER,V_EAST_OVER,V_IN_TEST1_TAB,V_IN_TEST2_TAB,SYSDATE);
    COMMIT;

  END;