The following code would help you to proceed further. It works fine for columns of data type number,varchar,char etc but not for LONG.
You will need to check the following:
1. If you want to check for all the tables of a database then you need to make sure that SELECT for all the tables is GRANTED to the schema from where you are running this code
2. Convert LONG TO VARCHAR explicitly and then do the comparison.
-
declare
-
cursor c1 is select table_name from all_tables WHERE owner = (SELECT user FROM dual);
-
col_names SYS_REFCURSOR;
-
TYPE dat_res IS RECORD(tab_name VARCHAR2(100),col_name VARCHAR2(100));
-
TYPE dr is table of dat_res;
-
act_dat dr:= dr();
-
status NUMBER := 0;
-
cnt NUMBER := 1;
-
sql_stmt VARCHAR2(10000);
-
tab_name1 VARCHAR2(100);
-
col_name1 VARCHAR2(100);
-
BEGIN
-
FOR I IN C1 LOOP
-
sql_stmt:= 'SELECT table_name,column_name FROM all_Tab_cols WHERE table_name = '||CHR(39)||I.table_name||CHR(39);
-
OPEN col_names FOR sql_stmt;
-
LOOP
-
FETCH col_names INTO tab_name1,col_name1;
-
EXIT WHEN col_names%NOTFOUND;
-
BEGIN
-
EXECUTE IMMEDIATE 'SELECT 1 FROM '||tab_name1|| ' WHERE '||'TO_CHAR('||col_name1||') LIKE '||CHR(39)||'BCD'||CHR(39) INTO status;
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
NULL;
-
WHEN OTHERS THEN
-
DBMS_OUTPUT.PUT_LINE('Error In table: '||tab_name1||' Column:'||col_name1||' '||SQLERRM);
-
END;
-
IF (status = 1) THEN
-
act_dat.extend;
-
act_dat(cnt).tab_name:= tab_name1;
-
act_dat(cnt).col_name:= col_name1;
-
status := 0;
-
cnt:= cnt + 1;
-
END IF;
-
END LOOP;
-
CLOSE col_names;
-
END LOOP;
-
dbms_output.put_line('Table Name : Column Name');
-
FOR K IN act_dat.FIRST..act_dat.LAST LOOP
-
dbms_output.put_line(act_dat(K).tab_name||' : '||act_dat(K).col_name);
-
END LOOP;
-
END;
-
-
SQL> /
-
Error In table: PLAN_TABLE Column:OTHER ORA-00932: inconsistent datatypes: expected CHAR got LONG
-
Error In table: PLAN_TABLE Column:OTHER ORA-00932: inconsistent datatypes: expected CHAR got LONG
-
Table Name : Column Name
-
EMP : EMPNAME
-
EMP1 : EMPNAME
-
-
PL/SQL procedure successfully completed.
-
-
SQL>
-
-
If you see above , it list the tables that were errored out due to column datatype LONG and the list of tables that contain the value 'BCD'
Just BUILD on this and create your code as per your requirement