Source Table ============ CREATE TABLE CONTEXT_UT ( START_DATE DATE, CONTEXTID VARCHAR2(64 BYTE), UTID VARCHAR2(64 BYTE), TCELL VARCHAR2(64 BYTE), CELLRESERVED VARCHAR2(64 BYTE), TRESELECTION VARCHAR2(64 BYTE), QUALMEASQTY VARCHAR2(64 BYTE), QHYST1 VARCHAR2(64 BYTE), QHYST2 VARCHAR2(64 BYTE), QQUALMIN VARCHAR2(64 BYTE), QRXLEVMIN VARCHAR2(64 BYTE), INDIVOFFSET VARCHAR2(64 BYTE), PWRADM VARCHAR2(64 BYTE), PWRADMOFFSET VARCHAR2(64 BYTE), PWROFFSET VARCHAR2(64 BYTE), PWRHYST VARCHAR2(64 BYTE), TMCONGACTION VARCHAR2(64 BYTE), RELEASEASEDL VARCHAR2(64 BYTE), ASEDLADM VARCHAR2(64 BYTE), DLCODEADM VARCHAR2(64 BYTE), ASEULADM VARCHAR2(64 BYTE), SF8ADM VARCHAR2(64 BYTE), SF32ADM VARCHAR2(64 BYTE), ASEULADMOFFSET VARCHAR2(64 BYTE), MINPWRRL VARCHAR2(64 BYTE), MAXRATE VARCHAR2(64 BYTE), INTERRATE VARCHAR2(64 BYTE), MINIMUMRATE VARCHAR2(64 BYTE), MAXPWRMAX VARCHAR2(64 BYTE), INTERPWRMAX VARCHAR2(64 BYTE), MINPWRMAX VARCHAR2(64 BYTE), COMPMODEADM VARCHAR2(64 BYTE), IFOFFSET VARCHAR2(64 BYTE), IFHYST VARCHAR2(64 BYTE), IFCONG VARCHAR2(64 BYTE), INTFQFDDMEAIND VARCHAR2(64 BYTE), SRATSEARCH VARCHAR2(64 BYTE), SINTRASEARCH VARCHAR2(64 BYTE), SINTERSEARCH VARCHAR2(64 BYTE), FACHMEAOCACYLNCOEF VARCHAR2(64 BYTE), ACCESSCLSNBARR VARCHAR2(64 BYTE), MAXTXPOWERUL VARCHAR2(64 BYTE), BEMARGINASEUL VARCHAR2(64 BYTE), BEMARGINASEDL VARCHAR2(64 BYTE), SIB1PLMNSPVALTAG VARCHAR2(64 BYTE), SF16ADM VARCHAR2(64 BYTE), BEMARGINDLPWR VARCHAR2(64 BYTE), BEMARGINDLCODE VARCHAR2(64 BYTE), HOTYPE VARCHAR2(64 BYTE), USEDFRQTHR2DECNO VARCHAR2(64 BYTE), USEDFRQTHR2DRSCP VARCHAR2(64 BYTE), ADMINSTATE VARCHAR2(64 BYTE), LDSHARGSMTHRES VARCHAR2(64 BYTE), LDSHARGSMFRACT VARCHAR2(64 BYTE), SNDIRRETRYTGT VARCHAR2(64 BYTE), NINSYNCIND VARCHAR2(64 BYTE), RLFAILURET VARCHAR2(64 BYTE), NOUTSYNCIND VARCHAR2(64 BYTE), SF4ADMUL VARCHAR2(64 BYTE), HARDIFHOCORR VARCHAR2(64 BYTE), HSDPAUSERSADM VARCHAR2(64 BYTE), LDSHARMARG VARCHAR2(64 BYTE), RELASEDLGHS VARCHAR2(64 BYTE), TMCONGACTGHS VARCHAR2(64 BYTE), TMINITIALGHS VARCHAR2(64 BYTE), SF4ULPATHLOSSTHRES VARCHAR2(64 BYTE), ULPATHLOSSCHECKENA VARCHAR2(64 BYTE), SHCSRAT VARCHAR2(64 BYTE), SF16GADM VARCHAR2(64 BYTE), RELEASEASEDLNG VARCHAR2(64 BYTE), TMCONGACTIONNG VARCHAR2(64 BYTE), TMINITIALG VARCHAR2(64 BYTE), SF16ADMUL VARCHAR2(64 BYTE), SF8ADMUL VARCHAR2(64 BYTE), POS VARCHAR2(2000 BYTE), RESERVEDBY VARCHAR2(20 BYTE), EULNOSRVCELLUSRADM VARCHAR2(64 BYTE), ACCCLBARREDCS VARCHAR2(64 BYTE), ACCCLBARREDPS VARCHAR2(64 BYTE), AGPSENABLED VARCHAR2(64 BYTE), ASLDTHUSP_AMR12200 VARCHAR2(64 BYTE), ASLDTHUSP_AMR7950 VARCHAR2(64 BYTE), ASLDTHUSP_AMR5900 VARCHAR2(64 BYTE), CDLDTHRDLSF128 VARCHAR2(64 BYTE), EULSRVCEUSRADM VARCHAR2(64 BYTE), IUBLINKREF VARCHAR2(256 BYTE), PWRLOADTHRDLSP VARCHAR2(512 BYTE), RTSELPSINT_CHANTYP VARCHAR2(64 BYTE), RTSELPSINT_ULPRFRT VARCHAR2(64 BYTE), RTSELPSINT_DLPRFRT VARCHAR2(64 BYTE), SF8GADMUL VARCHAR2(64 BYTE), AMRWBRATEDLMAX NUMBER, AMRWBRATEULMAX NUMBER, HCSSIB3CON_SSRCHHCS NUMBER, HCSUSG_CONECTEDMODE NUMBER, HCSUSG_IDLEMODE NUMBER ) Target Table ================== CREATE TABLE STG ( CONTEXTID VARCHAR2(32 BYTE), UTID VARCHAR2(32 BYTE), START_TSTAMP DATE, ATTRIBUTE_NAME VARCHAR2(50 BYTE), NEW_VALUE VARCHAR2(500 BYTE), OLD_VALUE VARCHAR2(500 BYTE) ) INSERT /* +append */ INTO STG (CONTEXTID, UTID, START_TSTAMP, ATTRIBUTE_NAME, NEW_VALUE, OLD_VALUE) WITH lst_qry AS (SELECT /*+ DRIVING_SITE(d) full(d) */ START_DATE, CONTEXTID, UTID, HCSSIB3CON_SSRCHHCS, HCSUSG_CONECTEDMODE, HCSUSG_IDLEMODE, AMRWBRATEDLMAX, AMRWBRATEULMAX, ASLDTHUSP_AMR12200, ASLDTHUSP_AMR5900, ASLDTHUSP_AMR7950, EULNOSRVCELLUSRADM, FACHMEAOCACYLNCOEF, RTSELPSINT_CHANTYP, RTSELPSINT_DLPRFRT, RTSELPSINT_ULPRFRT, SF4ULPATHLOSSTHRES, SIB1PLMNSPVALTAG, ULPATHLOSSCHECKENA, USEDFRQTHR2DECNO, USEDFRQTHR2DRSCP, ACCCLBARREDCS, ACCCLBARREDPS, ACCESSCLSNBARR, ADMINSTATE, AGPSENABLED, ASEDLADM, ASEULADM, ASEULADMOFFSET, BEMARGINASEDL, BEMARGINASEUL, BEMARGINDLCODE, BEMARGINDLPWR, CDLDTHRDLSF128, CELLRESERVED, COMPMODEADM, DLCODEADM, EULSRVCEUSRADM, HARDIFHOCORR, HOTYPE, HSDPAUSERSADM, IFCONG, IFHYST, IFOFFSET, INDIVOFFSET, INTERPWRMAX, INTERRATE, INTFQFDDMEAIND, IUBLINKREF, LDSHARGSMFRACT, LDSHARGSMTHRES, LDSHARMARG, MAXPWRMAX, MAXRATE, MAXTXPOWERUL, MINIMUMRATE, MINPWRMAX, MINPWRRL, NINSYNCIND, NOUTSYNCIND, PWRADM, PWRADMOFFSET, PWRHYST, PWRLOADTHRDLSP, PWROFFSET, QHYST1, QHYST2, QQUALMIN, QRXLEVMIN, QUALMEASQTY, RELASEDLGHS, RELEASEASEDL, RELEASEASEDLNG, RESERVEDBY, RLFAILURET, SF16ADM, SF16ADMUL, SF16GADM, SF32ADM, SF4ADMUL, SF8ADM, SF8ADMUL, SF8GADMUL, SHCSRAT, SINTERSEARCH, SINTRASEARCH, SNDIRRETRYTGT, SRATSEARCH, TCELL, TMCONGACTGHS, TMCONGACTION, TMCONGACTIONNG, TMINITIALG, TMINITIALGHS, TRESELECTION, POS FROM CONTEXT_UT@remote_DB d WHERE start_date BETWEEN TO_DATE ('02/15/2011', 'MM/DD/YYYY') - 3 AND TO_DATE ('02/15/2011', 'MM/DD/YYYY')) SELECT c_d.CONTEXTID, c_d.UTID, c_d.START_DATE, vals.param_name ATTRIBUTE_NAME, REPLACE (vals.param_value, 'NULL', '') new_value, REPLACE (vals.old_param_value, 'NULL', '') old_value FROM (SELECT * FROM LST_QRY WHERE START_DATE = TO_DATE ('02/15/2011', 'MM/DD/YYYY')) C_d, (SELECT * FROM (SELECT RANK () OVER (PARTITION BY CONTEXTID, UTID ORDER BY start_date DESC) sl, b.* FROM LST_QRY b WHERE START_DATE < TO_DATE ('02/15/2011', 'MM/DD/YYYY')) WHERE sl = 1) P_d, TABLE(Name_Value_varray ( Name_Value_Pair('HCSSIB3CON_SSRCHHCS', c_d.HCSSIB3CON_SSRCHHCS, p_d.HCSSIB3CON_SSRCHHCS), Name_Value_Pair('HCSUSG_CONECTEDMODE', c_d.HCSUSG_CONECTEDMODE, p_d.HCSUSG_CONECTEDMODE), Name_Value_Pair('HCSUSG_IDLEMODE', c_d.HCSUSG_IDLEMODE, p_d.HCSUSG_IDLEMODE), Name_Value_Pair('AMRWBRATEDLMAX', c_d.AMRWBRATEDLMAX, p_d.AMRWBRATEDLMAX), Name_Value_Pair('AMRWBRATEULMAX', c_d.AMRWBRATEULMAX, p_d.AMRWBRATEULMAX), Name_Value_Pair('ASLDTHUSP_AMR12200', c_d.ASLDTHUSP_AMR12200, p_d.ASLDTHUSP_AMR12200), Name_Value_Pair('ASLDTHUSP_AMR5900', c_d.ASLDTHUSP_AMR5900, p_d.ASLDTHUSP_AMR5900), Name_Value_Pair('ASLDTHUSP_AMR7950', c_d.ASLDTHUSP_AMR7950, p_d.ASLDTHUSP_AMR7950), Name_Value_Pair('EULNOSRVCELLUSRADM', c_d.EULNOSRVCELLUSRADM, p_d.EULNOSRVCELLUSRADM), Name_Value_Pair('FACHMEAOCACYLNCOEF', c_d.FACHMEAOCACYLNCOEF, p_d.FACHMEAOCACYLNCOEF), Name_Value_Pair('RTSELPSINT_CHANTYP', c_d.RTSELPSINT_CHANTYP, p_d.RTSELPSINT_CHANTYP), Name_Value_Pair('RTSELPSINT_DLPRFRT', c_d.RTSELPSINT_DLPRFRT, p_d.RTSELPSINT_DLPRFRT), Name_Value_Pair('RTSELPSINT_ULPRFRT', c_d.RTSELPSINT_ULPRFRT, p_d.RTSELPSINT_ULPRFRT), Name_Value_Pair('SF4ULPATHLOSSTHRES', c_d.SF4ULPATHLOSSTHRES, p_d.SF4ULPATHLOSSTHRES), Name_Value_Pair('SIB1PLMNSPVALTAG', c_d.SIB1PLMNSPVALTAG, p_d.SIB1PLMNSPVALTAG), Name_Value_Pair('ULPATHLOSSCHECKENA', c_d.ULPATHLOSSCHECKENA, p_d.ULPATHLOSSCHECKENA), Name_Value_Pair('USEDFRQTHR2DECNO', c_d.USEDFRQTHR2DECNO, p_d.USEDFRQTHR2DECNO), Name_Value_Pair('USEDFRQTHR2DRSCP', c_d.USEDFRQTHR2DRSCP, p_d.USEDFRQTHR2DRSCP), Name_Value_Pair('ACCCLBARREDCS', c_d.ACCCLBARREDCS, p_d.ACCCLBARREDCS), Name_Value_Pair('ACCCLBARREDPS', c_d.ACCCLBARREDPS, p_d.ACCCLBARREDPS), Name_Value_Pair('ACCESSCLSNBARR', c_d.ACCESSCLSNBARR, p_d.ACCESSCLSNBARR), Name_Value_Pair('ADMINSTATE', c_d.ADMINSTATE, p_d.ADMINSTATE), Name_Value_Pair('AGPSENABLED', c_d.AGPSENABLED, p_d.AGPSENABLED), Name_Value_Pair('ASEDLADM', c_d.ASEDLADM, p_d.ASEDLADM), Name_Value_Pair('ASEULADM', c_d.ASEULADM, p_d.ASEULADM), Name_Value_Pair('ASEULADMOFFSET', c_d.ASEULADMOFFSET, p_d.ASEULADMOFFSET), Name_Value_Pair('BEMARGINASEDL', c_d.BEMARGINASEDL, p_d.BEMARGINASEDL), Name_Value_Pair('BEMARGINASEUL', c_d.BEMARGINASEUL, p_d.BEMARGINASEUL), Name_Value_Pair('BEMARGINDLCODE', c_d.BEMARGINDLCODE, p_d.BEMARGINDLCODE), Name_Value_Pair('BEMARGINDLPWR', c_d.BEMARGINDLPWR, p_d.BEMARGINDLPWR), Name_Value_Pair('CDLDTHRDLSF128', c_d.CDLDTHRDLSF128, p_d.CDLDTHRDLSF128), Name_Value_Pair('CELLRESERVED', c_d.CELLRESERVED, p_d.CELLRESERVED), Name_Value_Pair('COMPMODEADM', c_d.COMPMODEADM, p_d.COMPMODEADM), Name_Value_Pair('DLCODEADM', c_d.DLCODEADM, p_d.DLCODEADM), Name_Value_Pair('EULSRVCEUSRADM', c_d.EULSRVCEUSRADM, p_d.EULSRVCEUSRADM), Name_Value_Pair('HARDIFHOCORR', c_d.HARDIFHOCORR, p_d.HARDIFHOCORR), Name_Value_Pair('HOTYPE', c_d.HOTYPE, p_d.HOTYPE), Name_Value_Pair('HSDPAUSERSADM', c_d.HSDPAUSERSADM, p_d.HSDPAUSERSADM), Name_Value_Pair('IFCONG', c_d.IFCONG, p_d.IFCONG), Name_Value_Pair('IFHYST', c_d.IFHYST, p_d.IFHYST), Name_Value_Pair('IFOFFSET', c_d.IFOFFSET, p_d.IFOFFSET), Name_Value_Pair('INDIVOFFSET', c_d.INDIVOFFSET, p_d.INDIVOFFSET), Name_Value_Pair('INTERPWRMAX', c_d.INTERPWRMAX, p_d.INTERPWRMAX), Name_Value_Pair('INTERRATE', c_d.INTERRATE, p_d.INTERRATE), Name_Value_Pair('INTFQFDDMEAIND', c_d.INTFQFDDMEAIND, p_d.INTFQFDDMEAIND), Name_Value_Pair('IUBLINKREF', c_d.IUBLINKREF, p_d.IUBLINKREF), Name_Value_Pair('LDSHARGSMFRACT', c_d.LDSHARGSMFRACT, p_d.LDSHARGSMFRACT), Name_Value_Pair('LDSHARGSMTHRES', c_d.LDSHARGSMTHRES, p_d.LDSHARGSMTHRES), Name_Value_Pair('LDSHARMARG', c_d.LDSHARMARG, p_d.LDSHARMARG), Name_Value_Pair('MAXPWRMAX', c_d.MAXPWRMAX, p_d.MAXPWRMAX), Name_Value_Pair('MAXRATE', c_d.MAXRATE, p_d.MAXRATE), Name_Value_Pair('MAXTXPOWERUL', c_d.MAXTXPOWERUL, p_d.MAXTXPOWERUL), Name_Value_Pair('MINIMUMRATE', c_d.MINIMUMRATE, p_d.MINIMUMRATE), Name_Value_Pair('MINPWRMAX', c_d.MINPWRMAX, p_d.MINPWRMAX), Name_Value_Pair('MINPWRRL', c_d.MINPWRRL, p_d.MINPWRRL), Name_Value_Pair('NINSYNCIND', c_d.NINSYNCIND, p_d.NINSYNCIND), Name_Value_Pair('NOUTSYNCIND', c_d.NOUTSYNCIND, p_d.NOUTSYNCIND), Name_Value_Pair('PWRADM', c_d.PWRADM, p_d.PWRADM), Name_Value_Pair('PWRADMOFFSET', c_d.PWRADMOFFSET, p_d.PWRADMOFFSET), Name_Value_Pair('PWRHYST', c_d.PWRHYST, p_d.PWRHYST), Name_Value_Pair('PWRLOADTHRDLSP', c_d.PWRLOADTHRDLSP, p_d.PWRLOADTHRDLSP), Name_Value_Pair('PWROFFSET', c_d.PWROFFSET, p_d.PWROFFSET), Name_Value_Pair('QHYST1', c_d.QHYST1, p_d.QHYST1), Name_Value_Pair('QHYST2', c_d.QHYST2, p_d.QHYST2), Name_Value_Pair('QQUALMIN', c_d.QQUALMIN, p_d.QQUALMIN), Name_Value_Pair('QRXLEVMIN', c_d.QRXLEVMIN, p_d.QRXLEVMIN), Name_Value_Pair('QUALMEASQTY', c_d.QUALMEASQTY, p_d.QUALMEASQTY), Name_Value_Pair('RELASEDLGHS', c_d.RELASEDLGHS, p_d.RELASEDLGHS), Name_Value_Pair('RELEASEASEDL', c_d.RELEASEASEDL, p_d.RELEASEASEDL), Name_Value_Pair('RELEASEASEDLNG', c_d.RELEASEASEDLNG, p_d.RELEASEASEDLNG), Name_Value_Pair('RESERVEDBY', c_d.RESERVEDBY, p_d.RESERVEDBY), Name_Value_Pair('RLFAILURET', c_d.RLFAILURET, p_d.RLFAILURET), Name_Value_Pair('SF16ADM', c_d.SF16ADM, p_d.SF16ADM), Name_Value_Pair('SF16ADMUL', c_d.SF16ADMUL, p_d.SF16ADMUL), Name_Value_Pair('SF16GADM', c_d.SF16GADM, p_d.SF16GADM), Name_Value_Pair('SF32ADM', c_d.SF32ADM, p_d.SF32ADM), Name_Value_Pair('SF4ADMUL', c_d.SF4ADMUL, p_d.SF4ADMUL), Name_Value_Pair('SF8ADM', c_d.SF8ADM, p_d.SF8ADM), Name_Value_Pair('SF8ADMUL', c_d.SF8ADMUL, p_d.SF8ADMUL), Name_Value_Pair('SF8GADMUL', c_d.SF8GADMUL, p_d.SF8GADMUL), Name_Value_Pair('SHCSRAT', c_d.SHCSRAT, p_d.SHCSRAT), Name_Value_Pair('SINTERSEARCH', c_d.SINTERSEARCH, p_d.SINTERSEARCH), Name_Value_Pair('SINTRASEARCH', c_d.SINTRASEARCH, p_d.SINTRASEARCH), Name_Value_Pair('SNDIRRETRYTGT', c_d.SNDIRRETRYTGT, p_d.SNDIRRETRYTGT), Name_Value_Pair('SRATSEARCH', c_d.SRATSEARCH, p_d.SRATSEARCH), Name_Value_Pair('TCELL', c_d.TCELL, p_d.TCELL), Name_Value_Pair('TMCONGACTGHS', c_d.TMCONGACTGHS, p_d.TMCONGACTGHS), Name_Value_Pair('TMCONGACTION', c_d.TMCONGACTION, p_d.TMCONGACTION), Name_Value_Pair('TMCONGACTIONNG', c_d.TMCONGACTIONNG, p_d.TMCONGACTIONNG), Name_Value_Pair('TMINITIALG', c_d.TMINITIALG, p_d.TMINITIALG), Name_Value_Pair('TMINITIALGHS', c_d.TMINITIALGHS, p_d.TMINITIALGHS), Name_Value_Pair('TRESELECTION', c_d.TRESELECTION, p_d.TRESELECTION), Name_Value_Pair('POS', c_d.POS, p_d.POS) )) vals WHERE C_d.CONTEXTID = P_d.CONTEXTID AND c_d.UTID = P_d.UTID AND vals.param_value IS NOT NULL AND REPLACE (vals.param_value, 'NULL', '') <> REPLACE (vals.old_param_value, 'NULL', '')