Hi All i am trying to fetch certain email patterns from oracle db 11g i have used the below queries
Unfortunately i have to scan through the complete schemas in order to fetch the value (@pqr.de) where ever it exists in which ever column and table it is residing , ideally this activity is to list out inactive email addresses (inactive eamil address i need to check seperately in the other system not by querying)
query
--desc dba_tab_cols
SET SERVEROUTPUT ON 100000
DECLARE
l_sql CLOB;
l_where CLOB;
l_result INT;
BEGIN
FOR i IN (SELECT table_name,
column_name,
Row_number()
over (
PARTITION BY table_name
ORDER BY column_name ) AS seq,
Count(*)
over (
PARTITION BY table_name ) AS cnt
FROM dba_tab_columns
WHERE data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2',
'VARCHAR2'
)) LOOP
IF i.seq = 1 THEN
l_sql := 'select '
||Chr(10);
l_where := 'where '
||Chr(10);
END IF;
l_sql := l_sql
|| ' max(case when '
||i.column_name
||' like ''%@pqr.de%'' then '
||Power(2, i.seq - 1)
||' else 0 end)+'
||Chr(10);
l_where := l_where
|| ' '
||i.column_name
||' is not null or'
||Chr(10);
IF i.seq = i.cnt THEN
l_sql := Rtrim(l_sql, '+'
||Chr(10))
||Chr(10)
||'from '
||i.table_name
||Chr(10)
||Substr(l_where, 1, Length(l_where) - 4);
dbms_output.Put_line('---------------------------------------');
dbms_output.Put_line(l_sql);
EXECUTE IMMEDIATE l_sql INTO l_result;
IF l_result > 0 THEN
dbms_output.Put_line('Found!!! l_result='
||l_result);
END IF;
END IF;
END LOOP;
END;
/
i am getting error
Error report –
ORA-00936: missing expression ORA-06512: at line 54 00936. 00000 - "missing expression" *Cause: *Action:
how to resolve the error , since i am trying to retreive the list of tables from the above query
TESTED QUERY – Failed at line 60
SET SERVEROUTPUT ON 100000
DECLARE
l_sql CLOB;
l_where CLOB;
l_result INT;
BEGIN
FOR i IN (SELECT owner,
table_name,
column_name,
Row_number()
over (
PARTITION BY table_name
ORDER BY column_name ) AS seq,
Count(*)
over (
PARTITION BY table_name ) AS cnt
FROM all_tab_columns
--WHERE owner not in ('LIST_OF_SCHEMAS') -- list relevant schemas
AND data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2',
'VARCHAR2'
)) LOOP
IF i.seq = 1 THEN
l_sql := 'select '
||Chr(10);
l_where := 'where '
||Chr(10);
END IF;
l_sql := l_sql
|| ' max(case when "'
||i.column_name
||'" like ''%@pqr.de%'' then '
||Power(2, i.seq - 1)
||' else 0 end)+'
||Chr(10);
l_where := l_where
|| ' "'
||i.column_name
||'" is not null or'
||Chr(10);
IF i.seq = i.cnt THEN
l_sql := Rtrim(l_sql, '+'
||Chr(10))
||Chr(10)
||'from "'
||i.owner
||'"."'
||i.table_name
||'"'
||Chr(10)
||Substr(l_where, 1, Length(l_where) - 4);
dbms_output.Put_line('---------------------------------------');
---dbms_output.Put_line(l_sql);
dbms_output.Put_line(dbms_lob.substr(l_sql, 4000, 1));
EXECUTE IMMEDIATE l_sql INTO l_result;
IF l_result > 0 THEN
dbms_output.Put_line('Found!!! l_result='
||l_result);
END IF;
END IF;
END LOOP;
END;
Error report –
Error report - ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 61 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: *Action:
Advertisement
Answer
Your approach basically works (though there are simpler methods, if you really have to do this; there are some alternative approaches here for example), so the error you are getting from the execution of a a particular l_sql value suggests a problem with that specific construction. A likely culprit is a table or column name which is a reserved word, which is confusing the parser. You may also have problems with quoted identifiers, and you are looking for tables across all schemas (including internal ones like SYS) but are not specifying the owner of each table.
Those issues can be avoided by adding double-quotes and the owner to your cursor and construction statements:
...
-- add owner to cursor
FOR i IN (SELECT owner,
table_name,
column_name,
Row_number()
over (
PARTITION BY table_name
ORDER BY column_name ) AS seq,
Count(*)
over (
PARTITION BY table_name ) AS cnt
-- possibly query all_ instead of dba_
FROM all_tab_columns
-- limit to schema you're interested in
WHERE owner in (USER) -- list relevant schemas
AND data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2',
'VARCHAR2'
)) LOOP
...
l_sql := l_sql
-- add double-quotes around column name
|| ' max(case when "'
||i.column_name
||'" like ''%@pqr.de%'' then '
||Power(2, i.seq - 1)
||' else 0 end)+'
||Chr(10);
l_where := l_where
-- add double-quotes around column name
|| ' "'
||i.column_name
||'" is not null or'
||Chr(10);
IF i.seq = i.cnt THEN
l_sql := Rtrim(l_sql, '+'
||Chr(10))
||Chr(10)
-- add double-quotes around table name, and prefix with owner, also quoted (just in case!)
||'from "'
||i.owner
||'"."'
||i.table_name
||'"'
||Chr(10)
||Substr(l_where, 1, Length(l_where) - 4);
...
i have tested got the below error
Error report –
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 19270, maximum: 4000) ORA-06512: at line 60
The query should be OK with CLOB values, so I suspect line 60 is now the output line, and l_sql is itself too big; in which case you can change that to:
dbms_output.Put_line(dbms_lob.substr(l_sql, 4000, 1));
Error report –
ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 61
This is probably when i.seq reaches 128; power(2, 127) is too big for your int result variable. Which if I’m reading this right means you have a table with 128 text columns? Not sure what the point of that is though.