Oracle生成data patching脚本

生成data patching脚本

DECLARE
  V_SQL     CLOB;
  V_SUB_STR       VARCHAR2(32767);
  V_OWNER         VARCHAR2(30) := 'schema'; --'
  V_TABLE_NAME    VARCHAR2(30) := 'table_name'; --'
  V_COUNT         INTEGER := 0;
  V_TEMP_CNT      INTEGER;
  V_PK_NUM        INTEGER;
  V_FLAG_TRIM     INTEGER := 0;  --IF NOT NEED TRIM TO COMPARE THEN SET TO 0
BEGIN

  SELECT COUNT(*) CNT
    INTO V_PK_NUM
    FROM ALL_CONSTRAINTS CON
   WHERE CON.OWNER = V_OWNER
     AND CON.TABLE_NAME = V_TABLE_NAME
     AND CON.CONSTRAINT_TYPE = 'P';

  WITH UNI_IDX AS
   (SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME
      FROM ALL_INDEXES
     WHERE V_PK_NUM = 0
       AND TABLE_OWNER = V_OWNER
       AND TABLE_NAME = V_TABLE_NAME
       AND UNIQUENESS = 'UNIQUE'
       AND ROWNUM = 1),
  PK_UNI AS
   (SELECT CON_COL.COLUMN_NAME PK_COL
      FROM ALL_CONSTRAINTS CON
      JOIN ALL_CONS_COLUMNS CON_COL
        ON CON.OWNER = CON_COL.OWNER
       AND CON.TABLE_NAME = CON_COL.TABLE_NAME
       AND CON.CONSTRAINT_NAME = CON_COL.CONSTRAINT_NAME
     WHERE V_PK_NUM = 1
       AND CON.OWNER = V_OWNER
       AND CON.TABLE_NAME = V_TABLE_NAME
       AND CON.CONSTRAINT_TYPE = 'P'
    
    UNION ALL
    
    SELECT IDX_COL.COLUMN_NAME PK_COL
      FROM ALL_IND_COLUMNS IDX_COL
      JOIN UNI_IDX
        ON UNI_IDX