Compare datatype of all the columns of tables with one configuration table in plsql

See more:

SQL

Oracle

I have one table CONFIG_PRAM which contains columns like- colname ,and datatype and many more details of existing tables.

Ex- CREATE TABLE CONFIG_PRAM ( colname varchar(40),
datatype varchar(40)
);

So basically I have to compare those columns and data type which present in CONFIG_PRAM table with the existing table’s column.

EX. - I have one existing table test1 table in database create table test1 ( employee_id NUMBER(6), sal NUMBER(6,8));

And if found any mismatch,I need to update CONFIG_PRAM table with correct data type.

FOR above one in CONFIG_PRAM table we have sal, number but in-actual it is number(6,8) in table so i have to update CONFIG_PRAM table with exact datatype.

I have tried like this:

select distinct colname , datatype from CONFIG_PRAM , all_tab_columns where upper(column_name)=upper(colname ) and data_type=datatype and table_name in ('TEST1')

But Suppose Table A has -Number(6,8) And CONFIG_PRAM table contain only-Number Then it is not giving correct results. Issue- This query is not comparing decimal values exactly , can you please provide a solution for this in sql/PLSQL

Can you please help me here.

What I have tried:

I have tried like this:

select distinct colname , datatype from CONFIG_PRAM , all_tab_columns where upper(column_name)=upper(colname ) and data_type=datatype and table_name in ('TEST1')

But Suppose Table A has -Number(6,8) And CONFIG_PRAM table contain only-Number Then it is not giving correct results. Issue- This query is not comparing decimal values exactly , can you please provide a solution for this in sql/PLSQL