#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    Oracle SP for comparing column values in table pairs


    Hi All,
    I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records. Can anyone help me to fine-tune dis??
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    845
    Rep Power
    387

    Cool


    Originally Posted by developer12
    Hi All,
    I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records. Can anyone help me to fine-tune dis??
    Show us your query.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    Code


    NOTE: all @ has been replaced by ^ marks as I was not able to post this reply beacuse of it.

    Tables prefixed with X are the temp tables to store data of DB-A.
    The report will be originally based on DB-B, so DB Links will not be required for ^PROD1.WORLD tables.
    This is a test region, so I have pointed to ^PROD1.WORLD to test with Prod Data.
    SEC_COMPARE_CONFIG is the config table containing the table_name to be reported, corresponding temp tables to store the data and the columns on which it is to be reported.
    There are in total 8 tables- 90 rows and 8 temp tables.
    SPOKE_TO_HUB_SEC_MTCH_TBL records the securities on which it is to be reported.
    HIST_DATA_COMPARE_TBL is the final results table.


    Here is the entire code:
    CREATE OR REPLACE PACKAGE SECURITY_COMPARE AS

    PROCEDURE PROCESS_RECORDS (IN_EFFECTIVE_DATE IN DATE,
    IN_PRIMARY_ASSET_ID IN VARCHAR2 DEFAULT NULL);
    PROCEDURE IDENTIFY_SECURITIES ( P_EFFECTIVE_DATE IN DATE,
    P_PRIMARY_ASSET_ID IN VARCHAR2 DEFAULT NULL);
    PROCEDURE RETREIVE_RECORDS_FROM_SPOKE;
    PROCEDURE COMPARE_RECORDS(p_err_msg OUT VARCHAR2);
    PROCEDURE INSERT_DATA_TO_TABLE ( v_target_table VARCHAR2, v_sql_to_run VARCHAR2, v_commit_after NUMBER);

    END SECURITY_COMPARE;
    /

    CREATE OR REPLACE PACKAGE BODY SECURITY_COMPARE AS

    /*Declared String for recording Dynamic SQL's*/
    LC_SQL VARCHAR2 (20000);


    PROCEDURE PROCESS_RECORDS(IN_EFFECTIVE_DATE IN DATE,
    IN_PRIMARY_ASSET_ID IN VARCHAR2 DEFAULT NULL)
    AS
    L_EFF_DATE DATE;
    L_PRIMARY_ASSET_ID VARCHAR2(100);
    k_err_msg VARCHAR2(100); --Error message displayed in case of NO discretionary records found.

    BEGIN
    L_EFF_DATE := IN_EFFECTIVE_DATE;
    L_PRIMARY_ASSET_ID := IN_PRIMARY_ASSET_ID;

    IDENTIFY_SECURITIES(L_EFF_DATE,L_PRIMARY_ASSET_ID); --Calling the Identify_Securities procedure to identify the securities older by 90 days from report effective date
    RETREIVE_RECORDS_FROM_SPOKE(); --Retreiving the historic records from the security tables into temporary tables.
    COMPARE_RECORDS(p_err_msg=>k_err_msg); --Compare the records and report the discrepencies into HIST_DATA_COMPARE_TBL table

    END PROCESS_RECORDS;

    PROCEDURE IDENTIFY_SECURITIES(P_EFFECTIVE_DATE IN DATE,
    P_PRIMARY_ASSET_ID IN VARCHAR2 DEFAULT NULL)
    AS
    P_EFF_DATE DATE; --Effective Date of the report
    P_PRIMARY_ID VARCHAR2(100); --Primary AssetID which is used to search based on specific security
    v_target_table VARCHAR2(500); --Variable indicating the Target table for inserting the data
    v_sql_to_run VARCHAR2(5000); --Variable to store the Dynamic SQL to be executed
    v_commit_after NUMBER; --Variable to define after how many records is COMMIT to be done

    BEGIN
    LC_SQL :='';
    P_EFF_DATE := P_EFFECTIVE_DATE;
    P_PRIMARY_ID := P_PRIMARY_ASSET_ID;

    /*Deleting Old Entries from SPOKE_TO_HUB_SEC_MTCH_TBL table*/
    LC_SQL := 'TRUNCATE TABLE SPOKE_TO_HUB_SEC_MTCH_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    IF(P_PRIMARY_ID is NULL) --In case records do not need to be identified on basis of specific security
    THEN
    /*Identify Securities older by 90days from report effective date*/
    v_target_table := ' SPOKE_TO_HUB_SEC_MTCH_TBL';
    v_sql_to_run := 'WITH T AS ('||
    ' SELECT R.PRIMARY_ASSET_ID PRIMARY_ASSET_ID_R,'||
    ' R.SECURITY_ALIAS SECURITY_ALIAS_R,'||
    ' R.LAST_HELD_DATE LAST_HELD_DATE_R,'||
    ' R.PREV_HELD_DATE PREV_HELD_DATE_R,'||
    ' Q.PRIMARY_ASSET_ID PRIMARY_ASSET_ID_Q,'||
    ' Q.SECURITY_ALIAS SECURITY_ALIAS_Q,'||
    ' COUNT(*) OVER(PARTITION BY Q.PRIMARY_ASSET_ID) CNT'||
    ' FROM SPOKE_SECURITIES^PROD.WORLD R,'||
    ' SECURITYDBO.SECURITY_MASTER^PROD1.WORLD Q'||
    ' WHERE SYS_OP_MAP_NONNULL(R.last_held_date) <> '||q'!'FF'!'||
    ' and ceil(R.last_held_date-to_date('||''''||P_EFF_DATE||''''||')) >= 0'||
    ' and ceil(R.last_held_date-to_date('||''''||P_EFF_DATE||''''||')) <= 60'||
    ' and R.PRIMARY_ASSET_ID=Q.PRIMARY_ASSET_ID'||
    ' )'||
    ' SELECT PRIMARY_ASSET_ID_R,'||
    ' SECURITY_ALIAS_R,'||
    ' LAST_HELD_DATE_R,'||
    ' PREV_HELD_DATE_R,'||
    ' PRIMARY_ASSET_ID_Q,'||
    ' SECURITY_ALIAS_Q'||
    ' FROM T'||
    ' WHERE CNT =1';
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    ELSE
    v_target_table := ' SPOKE_TO_HUB_SEC_MTCH_TBL';
    v_sql_to_run := 'WITH T AS ('||
    ' SELECT R.PRIMARY_ASSET_ID PRIMARY_ASSET_ID_R,'||
    ' R.SECURITY_ALIAS SECURITY_ALIAS_R,'||
    ' R.LAST_HELD_DATE LAST_HELD_DATE_R,'||
    ' R.PREV_HELD_DATE PREV_HELD_DATE_R,'||
    ' Q.PRIMARY_ASSET_ID PRIMARY_ASSET_ID_Q,'||
    ' Q.SECURITY_ALIAS SECURITY_ALIAS_Q,'||
    ' COUNT(*) OVER(PARTITION BY Q.PRIMARY_ASSET_ID) CNT'||
    ' FROM SPOKE_SECURITIES^PROD.WORLD R,'||
    ' SECURITYDBO.SECURITY_MASTER^PROD1.WORLD Q'||
    ' where R.PRIMARY_ASSET_ID='||''''||P_PRIMARY_ID||''''||
    ' and R.PRIMARY_ASSET_ID=Q.PRIMARY_ASSET_ID'||
    ' )'||
    ' SELECT PRIMARY_ASSET_ID_R,'||
    ' SECURITY_ALIAS_R,'||
    ' LAST_HELD_DATE_R,'||
    ' PREV_HELD_DATE_R,'||
    ' PRIMARY_ASSET_ID_Q,'||
    ' SECURITY_ALIAS_Q'||
    ' FROM T'||
    ' WHERE CNT =1';
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    END IF;

    LC_SQL := 'TRUNCATE TABLE HIST_DATA_COMPARE_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    END IDENTIFY_SECURITIES;


    PROCEDURE RETREIVE_RECORDS_FROM_SPOKE
    AS
    v_target_table VARCHAR2(500);
    v_sql_to_run VARCHAR2(5000);
    v_commit_after NUMBER;

    BEGIN

    LC_SQL :='';

    LC_SQL:= 'TRUNCATE TABLE X_SECMASTER_HISTORY_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_SEC_MASTER_DTL_HIST_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_SECMASTER_DTL_EXT_HST_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_EQUITY_HIST_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_EQUITY_DETAIL_HIST_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_FIXED_INCOME_HIST_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_FIXED_INCOME_DTL_EXT_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    LC_SQL:= 'TRUNCATE TABLE X_DERIVATIVES_HIST_TBL';
    EXECUTE IMMEDIATE LC_SQL;

    /*SECMASTER_HISTORY*/
    v_target_table := 'X_SECMASTER_HISTORY_TBL';
    v_sql_to_run := ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.SECMASTER_HISTORY^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.SECMASTER_HISTORY^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*SECURITY_MASTER_DETAIL_HIST*/
    v_target_table := 'X_SEC_MASTER_DTL_HIST_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.SECURITY_MASTER_DETAIL_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.SECURITY_MASTER_DETAIL_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*SECMASTER_DETAIL_EXT_HIST*/
    v_target_table := 'X_SECMASTER_DTL_EXT_HST_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.SECMASTER_DETAIL_EXT_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.SECMASTER_DETAIL_EXT_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*EQUITY_HIST*/
    v_target_table := 'X_EQUITY_HIST_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.EQUITY_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.EQUITY_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*EQUITY_DETAIL_HIST*/
    v_target_table := 'X_EQUITY_DETAIL_HIST_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.EQUITY_DETAIL_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.EQUITY_DETAIL_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*FIXED_INCOME_HIST*/
    v_target_table := 'X_FIXED_INCOME_HIST_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.FIXED_INCOME_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.FIXED_INCOME_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*FIXED_INCOME_DETAIL_EXT_HIST*/
    v_target_table := 'X_FIXED_INCOME_DTL_EXT_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.FIXED_INCOME_DETAIL_EXT_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.FIXED_INCOME_DETAIL_EXT_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    /*DERIVATIVES_HIST*/
    v_target_table := 'X_DERIVATIVES_HIST_TBL';
    v_sql_to_run:= ' SELECT /*+DRIVING_SITE(K)*/ K.* FROM SECURITYDBO.DERIVATIVES_HIST^PROD.WORLD K '||
    ' INNER JOIN SPOKE_TO_HUB_SEC_MTCH_TBL I'||
    ' ON K.SECURITY_ALIAS = I.SPOKE_SEC'||
    ' AND K.SRC_INTFC_INST = 140 '||
    ' and K.EFFECTIVE_DATE =(SELECT /*+DRIVING_SITE(H)*/ MAX (H.EFFECTIVE_DATE) FROM SECURITYDBO.DERIVATIVES_HIST^PROD.WORLD H WHERE'||
    ' H.SECURITY_ALIAS = K.SECURITY_ALIAS AND H.SRC_INTFC_INST = K.SRC_INTFC_INST)' ;
    v_commit_after := 0;
    INSERT_DATA_TO_TABLE(v_target_table,v_sql_to_run,v_commit_after);

    END RETREIVE_RECORDS_FROM_SPOKE;

    PROCEDURE COMPARE_RECORDS(p_err_msg OUT VARCHAR2)
    AS

    l_count NUMBER;
    l_err_msg VARCHAR2(100);

    TYPE T_SECURITIES is TABLE of HIST_DATA_COMPARE_TBL%rowtype;
    ttype T_SECURITIES;

    CURSOR C1
    IS
    SELECT TABLE_NAME, TEMP_TABLE, COLUMN_NAME from SEC_COMPARE_CONFIG
    where column_name='EFFECTIVE_DATE';

    CURSOR C2
    IS
    SELECT * FROM SEC_COMPARE_CONFIG where id <=82;

    C_REC SEC_COMPARE_CONFIG%rowtype;

    BEGIN
    LC_SQL :='';
    p_err_msg :='';

    FOR C_REC in C1
    loop

    LC_SQL:= ' SELECT /*+DRIVING_SITE(B)*/ /*+PARALLEL(A,100)*/ B.SECURITY_ALIAS, to_char(C.SPOKE_PAID), A.SECURITY_ALIAS,to_char(C.HUB_PAID),'||''''||C_REC.TABLE_NAME||''''||','||q'!'EFFECTIVE_DATE'!'||', '||
    ' NVL((cast(B.'||C_REC.COLUMN_NAME||' as VARCHAR2(100))),'||q'!'No Records Found'!'||'),'||
    ' NVL((cast(A.'||C_REC.COLUMN_NAME||' as VARCHAR2(100))),'||q'!'No Records Found'!'||')'||
    ' FROM '||C_REC.TEMP_TABLE||' A, SECURITYDBO.'||C_REC.TABLE_NAME ||'^PROD1.WORLD B,'||
    ' SPOKE_TO_HUB_SEC_MTCH_TBL C'||
    ' WHERE A.SRC_INTFC_INST=140'||
    ' AND B.SRC_INTFC_INST=140'||
    ' AND A.SECURITY_ALIAS=C.spoke_sec'||
    ' and b.security_alias=C.HUB_SEC'||
    ' AND a.effective_date <> (select max(h.effective_date) from SECURITYDBO.'||C_REC.TABLE_NAME||'^PROD1.WORLD H'||
    ' where h.security_alias=c.hub_sec and h.src_intfc_inst=140 )';
    EXECUTE IMMEDIATE LC_SQL BULK COLLECT into ttype;

    FORALL x in ttype.First..ttype.Last
    insert into HIST_DATA_COMPARE_TBL values ttype(x);
    commit;

    end loop;


    For C_REC in C2
    loop
    LC_SQL:= ' SELECT /*+DRIVING_SITE(B)*/ /*+PARALLEL(A,100)*/ B.SECURITY_ALIAS, to_char(C.SPOKE_PAID), A.SECURITY_ALIAS,to_char(C.HUB_PAID),'||''''||C_REC.TABLE_NAME||''''||','||''''||C_REC.COLUMN_NAME|| ''''||','||
    ' NVL((cast(B.'||C_REC.COLUMN_NAME||' as VARCHAR2(100))),'||q'!'No Records Found'!'||'),'||
    ' NVL((cast(A.'||C_REC.COLUMN_NAME||' as VARCHAR2(100))),'||q'!'No Records Found'!'||')'||
    ' FROM '||C_REC.TEMP_TABLE||' A, SECURITYDBO.'||C_REC.TABLE_NAME ||'^PROD1.WORLD B,'||
    ' SPOKE_TO_HUB_SEC_MTCH_TBL C'||
    ' WHERE A.SRC_INTFC_INST=140'||
    ' AND B.SRC_INTFC_INST=140'||
    ' AND A.SECURITY_ALIAS=C.spoke_sec'||
    ' and b.security_alias=C.HUB_SEC'||
    ' and b.effective_date=a.effective_date'||
    ' AND NVL((cast(A.'||C_REC.column_name||' as VARCHAR2(100))),'||q'!'No Records Found'!'||') <>'||
    ' NVL((cast(B.'||C_REC.column_name||' as VARCHAR2(100))),'||q'!'No Records Found'!'||')';
    EXECUTE IMMEDIATE LC_SQL BULK COLLECT into ttype;

    FORALL x in ttype.First..ttype.Last
    insert into HIST_DATA_COMPARE_TBL values ttype(x);
    commit;
    end loop;

    BEGIN
    select count(*) into l_count from HIST_DATA_COMPARE_TBL;
    if(l_count=0) then
    l_err_msg :='No records found';
    end if;
    END;
    END COMPARE_RECORDS;

    /*********************************************************************************
    NAME: INSERT_DATA_TO_TABLE
    DESCRIPTION: This procedure will insert the records into the target table based based on the data fetched using the sql to run variable.
    It also records the commit_after variable which defines that after how many records the insert needs to be committed.

    *********************************************************************************/

    PROCEDURE INSERT_DATA_TO_TABLE ( v_target_table VARCHAR2,
    v_sql_to_run VARCHAR2,
    v_commit_after NUMBER) IS

    v_limit_sql1 VARCHAR2(300) := ' ';
    v_limit_sql2 VARCHAR2(900) := ' ';
    v_plsql_to_run VARCHAR2(32767);

    BEGIN

    IF NVL(v_commit_after,0) <> 0 THEN

    v_limit_sql1:= ' LIMIT ' || TO_CHAR(v_commit_after) ;

    v_limit_sql2:= ' IF MOD(v_number_of_rows, ' || TO_CHAR(v_commit_after) || ' ) = 0 THEN ' ||
    ' COMMIT; ' ||
    ' END IF; ' ;
    END IF;

    v_plsql_to_run:= ' ' ||
    'DECLARE ' ||
    ' v_number_of_rows number:=0; ' ||
    ' ' ||
    ' TYPE MyType IS REF CURSOR; ' ||
    ' CV MyType; ' ||
    ' TYPE RecTyp IS TABLE OF ' || v_target_table || '%ROWTYPE; ' ||
    ' rec RecTyp; ' ||
    ' ' ||
    'BEGIN ' ||
    ' ' ||
    'OPEN CV FOR ' ||
    ' ' || REPLACE( v_sql_to_run, ';', ' ' ) || ' ; ' ||
    ' LOOP ' ||
    ' FETCH CV BULK COLLECT INTO rec ' || v_limit_sql1 || '; ' ||
    ' FORALL i IN 1..rec.COUNT ' ||
    ' INSERT /*+ APPEND */ INTO ' || v_target_table || ' VALUES rec(i); ' ||
    ' v_number_of_rows := v_number_of_rows + SQL%ROWCOUNT; ' ||
    ' ' || v_limit_sql2 || ' ' ||
    ' EXIT WHEN CV%NOTFOUND; ' ||
    ' ' ||
    ' END LOOP; ' ||
    ' COMMIT; ' ||
    ' CLOSE CV; ' ||
    'END; ';

    EXECUTE IMMEDIATE v_plsql_to_run;
    COMMIT;

    END INSERT_DATA_TO_TABLE;

    END SECURITY_COMPARE;
    /
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    845
    Rep Power
    387

    Wink


    Originally Posted by developer12
    NOTE: all @ has been replaced by ^ marks as I was not able to post this reply beacuse of it.
    . . . E t c . . .
    1) You can use the "code" tags to format your code.

    2) What it seems you are doing is for each table loop many times, once for each column you compare.

    I would try to query the tables only once for all column(s) using "UNION" and "UNPIVOT" . For example the following will give the list of primary keys and columns where the data differs:
    Code:
    SQL> SELECT *
      2    FROM (SELECT u.*, COUNT ( *) OVER (PARTITION BY empno, colnm) cn
      3            FROM (  SELECT DISTINCT *
      4                      FROM (SELECT empno, ename, job, TO_CHAR ( sal) sal FROM emp
      5                            UNION
      6                            SELECT empno, ename, job, TO_CHAR ( sal) FROM emp2)
      7         UNPIVOT (data FOR colnm IN  (ename, job, sal))) u )
      8   WHERE cn > 1
      9*  ORDER BY empno, colnm
    SQL> /
    
         EMPNO COLNM           DATA                 CN
    ---------- --------------- ------------ ----------
          7839 SAL             5000                  2
          7839 SAL             5100                  2
          7844 SAL             1699                  2
          7844 SAL             1500                  2
          7876 JOB             CLERK                 2
          7876 JOB             CLERK II              2
          7876 SAL             1200                  2
          7876 SAL             1100                  2
          7900 SAL             950                   2
          7900 SAL             1050                  2
          7902 JOB             ANALYST               2
          7902 JOB             ANALYST I             2
          7902 SAL             3000                  2
          7902 SAL             3199                  2
          7934 SAL             1300                  2
          7934 SAL             1400                  2
    
    16 rows selected.
    HTH
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    1) You can use the "code" tags to format your code.

    2) What it seems you are doing is for each table loop many times, once for each column you compare.

    I would try to query the tables only once for all column(s) using "UNION" and "UNPIVOT" . For example the following will give the list of primary keys and columns where the data differs:
    Code:
    SQL> SELECT *
      2    FROM (SELECT u.*, COUNT ( *) OVER (PARTITION BY empno, colnm) cn
      3            FROM (  SELECT DISTINCT *
      4                      FROM (SELECT empno, ename, job, TO_CHAR ( sal) sal FROM emp
      5                            UNION
      6                            SELECT empno, ename, job, TO_CHAR ( sal) FROM emp2)
      7         UNPIVOT (data FOR colnm IN  (ename, job, sal))) u )
      8   WHERE cn > 1
      9*  ORDER BY empno, colnm
    SQL> /
    
         EMPNO COLNM           DATA                 CN
    ---------- --------------- ------------ ----------
          7839 SAL             5000                  2
          7839 SAL             5100                  2
          7844 SAL             1699                  2
          7844 SAL             1500                  2
          7876 JOB             CLERK                 2
          7876 JOB             CLERK II              2
          7876 SAL             1200                  2
          7876 SAL             1100                  2
          7900 SAL             950                   2
          7900 SAL             1050                  2
          7902 JOB             ANALYST               2
          7902 JOB             ANALYST I             2
          7902 SAL             3000                  2
          7902 SAL             3199                  2
          7934 SAL             1300                  2
          7934 SAL             1400                  2
    
    16 rows selected.
    HTH
    Thanks.
    Any idea/alternatives how I should proceed? Other than looping each time through a column?
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    845
    Rep Power
    387

    Cool


    Originally Posted by developer12
    Thanks.
    Any idea/alternatives how I should proceed? Other than looping each time through a column?
    1) Create a work table that contains the table(s) and corresponding column name(s) to compare.

    2) For each table, generate a text string to build a statement similar to the one I posted and execute it.

    3) Bulk collect the result and insert into another table.

    4) Using the result table, you can either generate a report or using the primary key, query the differing rows.

    Good luck!

    PS: Notice that numeric columns are converted to text for the UNPIVOT to work.
    Last edited by LKBrwn_DBA; August 16th, 2013 at 11:17 AM.

IMN logo majestic logo threadwatch logo seochat tools logo