代码之家 › 专栏 › 技术社区 › Imran Hemani

并行查询服务器P000 ORA-01722中发出错误信号:无效号码

oracle sql

Imran Hemani · 技术社区 · 6 年前

error signaled in parallel query server P000
ORA-01722: invalid number

执行代码时

有一个从表中获取值的游标

   /* VALIDATE MANDATORY CFA FIELDS START */
   CURSOR c_cfa_fields IS
      select storage_col_name,
             view_col_name
        from cfa_attrib
       where group_id    = 150
         and value_req   = 'Y'
       order by display_seq;
   ---
   TYPE cfa_fields_tbl IS TABLE OF c_cfa_fields%ROWTYPE;
   cfa_fields_rec      cfa_fields_tbl;
   /* VALIDATE MANDATORY CFA FIELDS END */

然后获取光标:

  OPEN c_cfa_fields;
   FETCH c_cfa_fields BULK COLLECT INTO cfa_fields_rec;
   CLOSE c_cfa_fields;
   ---
   IF cfa_fields_rec.COUNT > 0 THEN -- Skip if no mandatory CFA Field exists for the GROUP_ID
      FOR i IN 1 .. cfa_fields_rec.COUNT
      LOOP
         L_val_query := 'INSERT /*+ APPEND  NOLOGGING PARALLEL */ INTO dmf_val_error_log' || CHR(10) ||
                        'SELECT /*+ PARALLEL (s,50) */ ' || CHR(10) ||
                        '        ''' || I_TABLE_NAME || ''', -- error_table ' || CHR(10) ||
                        '        ''STORE='' || s.store || ''; GROUP_ID='' || s.group_id, -- pk_value' || CHR(10) ||
                        '        ''' || cfa_fields_rec(i).storage_col_name || ''', -- error_column' || CHR(10) ||
                        '        ''' || cfa_fields_rec(i).storage_col_name || '='' || ' ||
                           'NVL(s.'|| cfa_fields_rec(i).storage_col_name || ',''(null)''), -- error_value' || CHR(10) ||
                        '        ''' || cfa_fields_rec(i).view_col_name || ' (' ||
                           cfa_fields_rec(i).storage_col_name || ') cannot be NULL'', -- error_desc' || CHR(10) ||
                        '        SYSDATE  -- error_datetime' || CHR(10) ||
                        '  FROM ' || I_TABLE_NAME || ' s' || CHR(10) ||
                        ' WHERE ' || cfa_fields_rec(i).storage_col_name || ' IS NULL';
         ---
         EXECUTE IMMEDIATE L_val_query;

我注意到一件事: 它给出了错误。它适用于存储列名称的VARCHAR2数据类型

STORAGE_COL_NAME    VIEW_COL_NAME
VARCHAR2_1  LATITUDE
VARCHAR2_2  LONGITUDE
VARCHAR2_3  IS_CROSS_DOCK
VARCHAR2_4  CLEARANCE_STORE
VARCHAR2_5  CLIMATE
VARCHAR2_6  DEMOGRAPHY
NUMBER_11   DEFAULT_WH

0 回复 | 直到 6 年前

William Robertson 6 年前

当在动态生成的代码中发生错误时,有必要检查实际生成的代码并对其进行测试。

测试以再现您的情况:

declare 
    cursor c_cfa_fields is
        with cfa_attrib (storage_col_name, view_col_name) as
              ( select 'VARCHAR2_1', 'LATITUDE' from dual union all
                select 'VARCHAR2_2', 'LONGITUDE' from dual union all
                select 'VARCHAR2_3', 'IS_CROSS_DOCK' from dual union all
                select 'VARCHAR2_4', 'CLEARANCE_STORE' from dual union all
                select 'VARCHAR2_5', 'CLIMATE' from dual union all
                select 'VARCHAR2_6', 'DEMOGRAPHY' from dual union all
                select 'NUMBER_11', 'DEFAULT_WH' from dual )
        select storage_col_name, view_col_name
        from   cfa_attrib;

    type cfa_fields_tbl is table of c_cfa_fields%rowtype;
    cfa_fields_rec      cfa_fields_tbl;
    l_val_query         long;
    i_table_name        varchar2(30) := 'SAMPLE_TABLE_NAME';
begin
    open c_cfa_fields;
    fetch c_cfa_fields bulk collect into cfa_fields_rec;
    close c_cfa_fields;

    if cfa_fields_rec.count > 0 then -- skip if no mandatory cfa field exists for the group_id
        for i in 1 .. cfa_fields_rec.count loop
            l_val_query  := 'INSERT /*+ APPEND PARALLEL */ INTO dmf_val_error_log' || chr(10) ||
                            'SELECT /*+ PARALLEL (s,50) */ ' || chr(10) ||
                            '        ''' || i_table_name || ''', -- error_table ' || chr(10) ||
                            '        ''STORE='' || s.store || ''; GROUP_ID='' || s.group_id, -- pk_value' || chr(10) ||
                            '        ''' || cfa_fields_rec(i).storage_col_name || ''', -- error_column' || chr(10) ||
                            '        ''' || cfa_fields_rec(i).storage_col_name || '='' || ' ||
                               'NVL(s.'|| cfa_fields_rec(i).storage_col_name || ',''(null)''), -- error_value' || chr(10) ||
                            '        ''' || cfa_fields_rec(i).view_col_name || ' (' ||
                               cfa_fields_rec(i).storage_col_name || ') cannot be NULL'', -- error_desc' || chr(10) ||
                            '        SYSDATE  -- error_datetime' || chr(10) ||
                            'FROM   ' || i_table_name || ' s' || chr(10) ||
                            'WHERE  ' || cfa_fields_rec(i).storage_col_name || ' IS NULL';

            dbms_output.put_line(l_val_query);
            dbms_output.new_line();
        end loop;
    end if;
end;

with 子句来生成示例数据,而不是读取实际的表,我只是打印生成的插入,而不是执行它们(我不知道您的 dmf_val_error_log 看起来像,或者是在生成的代码中动态查询的表)。

(我去掉了这个词 NOLOGGING 从你的暗示。如果你愿意的话 dmf值错误日志 先不看,然后用 alter table dmf_val_error_log nologging

INSERT /*+ APPEND PARALLEL */ INTO dmf_val_error_log
SELECT /*+ PARALLEL (s,50) */ 
        'SAMPLE_TABLE_NAME', -- error_table 
        'STORE=' || s.store || '; GROUP_ID=' || s.group_id, -- pk_value
        'VARCHAR2_1', -- error_column
        'VARCHAR2_1=' || NVL(s.VARCHAR2_1,'(null)'), -- error_value
        'LATITUDE (VARCHAR2_1) cannot be NULL', -- error_desc
        SYSDATE  -- error_datetime
FROM   SAMPLE_TABLE_NAME s
WHERE  VARCHAR2_1 IS NULL

通过WITH子句替换伪数据,可以测试不同数据类型的行为。

如果 VARCHAR2_1 列是一个字符串,它可以工作:

with sample_table_name(store, group_id, varchar2_1) as
     ( select 'London', 123, cast(null as varchar2(1)) from dual )
select 'SAMPLE_TABLE_NAME', -- error_table 
       'STORE=' || s.store || '; GROUP_ID=' || s.group_id, -- pk_value
       'VARCHAR2_1', -- error_column
       'VARCHAR2_1=' || nvl(s.varchar2_1,'(null)'), -- error_value
       'LATITUDE (VARCHAR2_1) cannot be NULL', -- error_desc
       sysdate  -- error_datetime
from   sample_table_name s
where  varchar2_1 is null;

如果是数字,则失败:

with sample_table_name(store, group_id, varchar2_1) as
     ( select 'London', 123, cast(null as number) from dual )
select 'SAMPLE_TABLE_NAME', -- error_table 
       'STORE=' || s.store || '; GROUP_ID=' || s.group_id, -- pk_value
       'VARCHAR2_1', -- error_column
       'VARCHAR2_1=' || nvl(s.varchar2_1,'(null)'), -- error_value
       'LATITUDE (VARCHAR2_1) cannot be NULL', -- error_desc
       sysdate  -- error_datetime
from   sample_table_name s
where  varchar2_1 is null;

ERROR at line 6:
ORA-01722: invalid number

本质上,它是在尝试这样的事情:

select nvl(1, '(null)')
from   dual;

select nvl(to_char(1), '(null)')
from   dual;

将其应用到代码中,应该是

l_val_query  := 'INSERT /*+ APPEND PARALLEL */ INTO dmf_val_error_log' || chr(10) ||
                'SELECT /*+ PARALLEL (s,50) */ ' || chr(10) ||
                '       ''' || i_table_name || ''', -- error_table ' || chr(10) ||
                '       ''STORE='' || s.store || ''; GROUP_ID='' || s.group_id, -- pk_value' || chr(10) ||
                '       ''' || cfa_fields_rec(i).storage_col_name || ''', -- error_column' || chr(10) ||
                '       ''' || cfa_fields_rec(i).storage_col_name || '='' || ' ||
                        'NVL(to_char(s.'|| cfa_fields_rec(i).storage_col_name || '),''(null)''), -- error_value' || chr(10) ||
                '       ''' || cfa_fields_rec(i).view_col_name || ' (' ||
                   cfa_fields_rec(i).storage_col_name || ') cannot be NULL'', -- error_desc' || chr(10) ||
                '       SYSDATE  -- error_datetime' || chr(10) ||
                'FROM   ' || i_table_name || ' s' || chr(10) ||
                'WHERE  ' || cfa_fields_rec(i).storage_col_name || ' IS NULL';

生成如下插入语句:

INSERT /*+ APPEND PARALLEL */ INTO dmf_val_error_log
SELECT /*+ PARALLEL (s,50) */ 
       'SAMPLE_TABLE_NAME', -- error_table 
       'STORE=' || s.store || '; GROUP_ID=' || s.group_id, -- pk_value
       'VARCHAR2_1', -- error_column
       'VARCHAR2_1=' || NVL(to_char(s.VARCHAR2_1),'(null)'), -- error_value
       'LATITUDE (VARCHAR2_1) cannot be NULL', -- error_desc
       SYSDATE  -- error_datetime
FROM   SAMPLE_TABLE_NAME s
WHERE  VARCHAR2_1 IS NULL