时间戳

首页 > 解决方案 > 如何从oracle数据库中获取电子邮件地址

sql - 如何从oracle数据库中获取电子邮件地址

问题描述

大家好，我正在尝试从 oracle db 11g 获取某些电子邮件模式，我使用了以下查询

不幸的是，我必须扫描完整的模式才能获取它所在的列和表中存在的值（@pqr.de），理想情况下，此活动是列出不活动的电子邮件地址（不活动的电子邮件地址 i需要在其他系统中单独检查而不是通过查询）

询问

--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; 

/

我收到错误

错误报告 -

ORA-00936: missing expression
ORA-06512: at line 54
00936. 00000 -  "missing expression"
*Cause:    
*Action:

如何解决错误，因为我正在尝试从上述查询中检索表列表

TESTED QUERY - 在第 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 -
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:

标签： sqloracleplsql

解决方案

您的方法基本上有效（尽管有更简单的方法，如果您真的必须这样做；例如，这里有一些替代方法），因此您从执行特定l_sql值中得到的错误表明该特定构造存在问题。可能的罪魁祸首是作为保留字的表或列名，这会使解析器感到困惑。您可能还会遇到带引号的标识符的问题，并且您正在寻找跨所有模式（包括 SYS 等内部模式）的表，但没有指定每个表的所有者。

可以通过在光标和构造语句中添加双引号和所有者来避免这些问题：

...
-- 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); 
...

db<>小提琴

我已经测试了以下错误

错误报告 -

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 19270, maximum: 4000)
ORA-06512: at line 60

查询应该可以使用 CLOB 值，所以我怀疑第 60 行现在是输出行，l_sql它本身太大了；在这种情况下，您可以将其更改为：

          dbms_output.Put_line(dbms_lob.substr(l_sql, 4000, 1));

错误报告 -

ORA-06502：PL/SQL：数字或值错误：数字精度太大 ORA-06512：在第 61 行

这大概是i.seq达到128的时候；对于您的结果变量power(2, 127)来说太大了。int如果我没看错，这意味着您有一个包含 128 个文本列的表格？不知道那是什么意思。

推荐阅读