CODEWITHSUNDEEP
oracle-databaseplsql
PKey
PKey

Reputation: 3841

Diffing 2 rows of 2 tables based on column values (without prior knowledge of columns names)

I don't even know if it is possible - so any idea will be welcome:

I want a function, which will return a string(varchar2) representation of the differences in values of columns of 2 specific tables.

So it's task will be to

find difference between 2 rows that belong to 2 tables (which happens to have the same structure).

Consider the following scenario.

Table A (A_rowid,col1,col2,col3,col4,col5...,coln) and values (id1,val1,val2,val3,..,valn)

Table B (B_rowid,col1,col2,col3,col4,col5...,coln) and values (id2,val1,val2',val3,..,valn')

*A_rowid - unique key of tableA, B_rowid - unique key of table B

fnction diff(A_rowid number, B_rowid number) returns varchar2 is 
begin 
--do something 
end;

All columns of the tables could be treated as Varchar2.

Thus,

The expected output would be -->

Null if no difference is found

or

diff: col2:val2->val2', coln:valn->valn'

What is important here is that I would like to do that without hard coding column names

(table names are hard coded though).

e.g. if and when we add additional columns to our tables - function should still work.

Upvotes: 0

Views: 74

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

You can use this one:

FUNCTION diff(A_rowid NUMBER, B_rowid NUMBER) RETURN VARCHAR2 IS 
    CURSOR TabColumns IS
    SELECT COLUMN_NAME, COLUMN_ID
    FROM USER_TAB_COLUMNS 
    WHERE TABLE_NAME = 'TABLE_A' 
        AND COLUMN_NAME <> 'A_ROWID' 
    ORDER BY COLUMN_ID;

    sqlstr VARCHAR2(1000);
    val_a VARCHAR2(4000);
    val_b VARCHAR2(4000);
    res VARCHAR2(30000);
BEGIN 

    FOR aCol IN TabColumns  LOOP
    BEGIN
        sqlstr := 'SELECT a.'||aCol.COLUMN_NAME||', b.'||aCol.COLUMN_NAME;
        sqlstr := sqlstr ||' FROM TABLE_A a CROSS JOIN TABLE_B b ';
        sqlstr := sqlstr || ' WHERE A_rowid = :aRow AND B_rowid = :bRow ';
        sqlstr := sqlstr || ' AND LNNVL(a.'||aCol.COLUMN_NAME||' = b.'||aCol.COLUMN_NAME||') ';
        sqlstr := sqlstr || ' AND COALESCE(a.'||aCol.COLUMN_NAME||', b.'||aCol.COLUMN_NAME||') IS NOT NULL ';
        EXECUTE IMMEDIATE sqlstr INTO val_a, val_b USING A_rowid, B_rowid;
        res := res ||', '||aCol.COLUMN_NAME||':'||val_a||'->'||val_b;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
    END;
    END LOOP;

    RETURN REGEXP_REPLACE(res, '^, ', 'diff:');

END;

Note, function LNNVL(a.'||aCol.COLUMN_NAME||' = b.'||aCol.COLUMN_NAME||') is needed in case of NULL values.

Condition a.COLUMN_NAME <> b.COLUMN_NAME does return anything when one of the values is NULL.

LNNVL(a.COLUMN_NAME = b.COLUMN_NAME) is equivalent to 

( a.COLUMN_NAME <> b.COLUMN_NAME 
   OR (a.COLUMN_NAME IS NULL AND b.COLUMN_NAME IS NOT NULL) 
   OR (a.COLUMN_NAME IS NOT NULL AND b.COLUMN_NAME IS NULL) )

However, use function above only if you are not concerned about performance. The more advanced solution would be this one:

FUNCTION diff(A_rowid NUMBER, B_rowid NUMBER) RETURN VARCHAR2 IS 

    CURSOR TabColumns IS
    SELECT COLUMN_NAME, COLUMN_ID
    FROM USER_TAB_COLUMNS 
    WHERE TABLE_NAME = 'TABLE_A' 
        AND COLUMN_NAME <> 'A_ROWID' 
    ORDER BY COLUMN_ID;

    sqlstr VARCHAR2(10000);
    val_a VARCHAR2(4000);
    val_b VARCHAR2(4000);
    res VARCHAR2(30000);

    cur INTEGER;
    p INTEGER;
    res INTEGER;

BEGIN 

    sqlstr := 'SELECT '
    FOR aCol IN TabColumns LOOP
        sqlstr := ' a.'||aCol.COLUMN_NAME||'_A, b.'||aCol.COLUMN_NAME||'_B, ';
    END LOOP;
    sqlstr := REGEXP_REPLACE(sqlstr, ', $', ' FROM TABLE_A a CROSS JOIN TABLE_B b ');
    sqlstr := sqlstr || ' WHERE A_rowid = :aRow AND B_rowid = :bRow ';

    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE (cur, ':aRow', A_rowid);
    DBMS_SQL.BIND_VARIABLE (cur, ':bRow', B_rowid);
    p := 1;
    FOR aCol IN TabColumns LOOP
        DBMS_SQL.DEFINE_COLUMN(cur, p, aCol.COLUMN_NAME||'_A', 4000);
        DBMS_SQL.DEFINE_COLUMN(cur, p+1, aCol.COLUMN_NAME||'_B', 4000);
        p := p + 2;
    END LOOP;
    res := DBMS_SQL.EXECUTE_AND_FETCH(cur, TRUE);

    p := 1;
    FOR aCol IN TabColumns LOOP
        DBMS_SQL.COLUMN_VALUE(cur, p, val_a);
        DBMS_SQL.COLUMN_VALUE(cur, p+1, val_b);
        p := p + 2;
        IF val_a <> val_b OR (val_a IS NULL AND val_b IS NOT NULL) OR (val_a IS NOT NULL AND val_b IS NULL) THEN
            res := res ||', '||aCol.COLUMN_NAME||':'||val_a||'->'||val_b;
        END IF;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cur);

    RETURN REGEXP_REPLACE(res, '^, ', 'diff:');

END;

Upvotes: 2

Sanjay Radadiya
Sanjay Radadiya

Reputation: 1286

Try this function

FUNCTION getdiff(arowid varchar, browid varchar) RETURN CLOB IS 
  v_line    clob;
  v_col_cnt INTEGER;
  v_ind     NUMBER;
  rec_tab   dbms_sql.desc_tab;
  v_cursor  NUMBER;
  v_sql     clob;
  V_FIRST   clob;V_SECOND CLOB;
  V_FINAL   CLOB;
begin

  V_SQL := Q'$ select * from(select * from Table1 where rowid=:arowid)a,
               (select * from Table2 where rowid=:browid)b $';

  V_CURSOR := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);

  DBMS_SQL.BIND_VARIABLE (V_CURSOR, ':arowid', arowid);
  DBMS_SQL.BIND_VARIABLE (V_CURSOR, ':browid', browid);

  DBMS_SQL.DESCRIBE_COLUMNS(V_CURSOR, V_COL_CNT, REC_TAB);
  FOR V_POS IN 1 .. REC_TAB.LAST LOOP
    V_LINE := REC_TAB(V_POS).COL_NAME;
    DBMS_SQL.DEFINE_COLUMN(V_CURSOR, V_POS, V_LINE);
  END LOOP;
  V_IND := DBMS_SQL.EXECUTE(V_CURSOR);

  LOOP
    V_IND := DBMS_SQL.FETCH_ROWS(V_CURSOR);
    EXIT WHEN V_IND = 0;
    FOR V_COL_SEQ IN 1 .. REC_TAB.COUNT LOOP
      if v_col_seq <=V_COL_CNT/2 then
         DBMS_SQL.COLUMN_VALUE(V_CURSOR, V_COL_SEQ, V_LINE);
         V_FIRST := V_LINE;
         DBMS_SQL.COLUMN_VALUE(V_CURSOR, V_COL_SEQ+3, V_LINE);
         V_SECOND := V_LINE;
         IF V_FIRST <> V_SECOND THEN
           V_FINAL := V_FINAL || rec_tab(v_col_seq).col_name || ':' || V_FIRST ||'->'||V_SECOND || ',';
         END IF;
      end if; 
    END LOOP;
  END LOOP;
  RETURN V_FINAL;
end;

output of getdiff function is in clob format because limit of varchar2 datatype is 32767 so after reach limit function give us error.

Use:

select to_char(getdiff('AAAjOuAAEAAA697AAC','AAAjOuAAEAAA697AAk')) from dual;

here to_char function is used for convert clob data format to char,so thats give us perfect string output.

Upvotes: 1

Related Questions