OK, here's one way to do it. All in SQL!
First up, to get the output you requested you'll need to convert the columns to rows. Unpivot helps with this:
select * from test
unpivot (
val for col in (
col1 as 'COL1', col2 as 'COL2', col3 as 'COL3', col4 as 'COL4'
)
);
Add other columns to the unpivot clause as needed.
Once you've done this, you can join it to user_tab_cols. This holds the data_default so you can compare to see if it matches this.
But there's a problem. User_tab_cols.data_default is a long. You can't just join this:
with rws as (
select * from test
unpivot (
val for col in (
col1 as 'COL1', col2 as 'COL2', col3 as 'COL3', col4 as 'COL4'
)
)
)
select * from rws
join user_tab_cols
on col = column_name
and val <> data_default
where table_name = 'TEST';
ORA-00997: illegal use of LONG datatype
So you need to convert data_default to a usable type. Adrian Billington discusses several methods to do this at:
http://www.oracle-developer.net/display.php?id=430 I've gone with the "convert to XML and extract the values" method:
with xml as (
select dbms_xmlgen.getxmltype(
'select column_name, data_default
from user_tab_cols where table_name = ''TEST''') x
from dual
), col_defs as (
select extractValue(xt.object_value, '/ROW/COLUMN_NAME') as col_name,
extractValue(xt.object_value, '/ROW/DATA_DEFAULT') as d_def
from xml, table(xmlsequence(extract(xml.x, '/ROWSET/ROW'))) xt
)
select * from col_defs;
Put it all together and you've got:
create table test (
test_id number not null,
employee_id number not null,
type varchar2(10),
col1 number default -1,
col2 number default -1,
col3 number default -1,
col4 number default -1,
primary key(test_id,employee_id)
);
insert into test values (100,999,'Type-1',5,-1,-1,4);
insert into test values (101,999,'Type-2',-1,2,-1,-1);
insert into test values (102,999,'Type-3',-1,2,-1,-1);
insert into test values (103,999,'Type-3',default,default,default,default);
with rws as (
select * from test
unpivot (
val for col in (
col1 as 'COL1', col2 as 'COL2', col3 as 'COL3', col4 as 'COL4'
)
)
), xml as (
select dbms_xmlgen.getxmltype(
'select column_name, data_default
from user_tab_cols where table_name = ''TEST''') x
from dual
), col_defs as (
select extractValue(xt.object_value, '/ROW/COLUMN_NAME') as col_name,
extractValue(xt.object_value, '/ROW/DATA_DEFAULT') as d_def
from xml, table(xmlsequence(extract(xml.x, '/ROWSET/ROW'))) xt
)
select r.*
from rws r
join col_defs c
on c.col_name = r.col
and c.d_def <> r.val;
TEST_ID EMPLOYEE_ID TYPE COL VAL
100 999 Type-1 COL1 5
102 999 Type-3 COL2 2
101 999 Type-2 COL2 2
100 999 Type-1 COL4 4