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

Question

I am getting the error:

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

while executing the code

There is a cursor that fetches the values from the table

   /* 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 */

I then fetch the cursor:

  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;

One thing I have noticed: If the Datatype of storage_col_name is NUMBER, it gives the error. It works fine for VARCHAR2 data type for storage_col_name

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

Answer 1

When errors occur within dynamically generated code, it's worth checking what is actually being generated and testing it.

Test to reproduce your situation:

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;

I've used a with clause to generate your sample data, rather than reading an actual table, and I just print the generated INSERTs rather than executing them (I don't know what your dmf_val_error_log looks like, or the table you are dynamically querying in the generated code).

(I took out the word NOLOGGING from your hint. If you want dmf_val_error_log to be nologging, then use alter table dmf_val_error_log nologging. There's no hint for it.)

The output is a series of INSERTs like this:

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

Substituting dummy data via a WITH clause, you can test how that will behave with different datatypes.

If the VARCHAR2_1 column is a string, it works:

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;

If it's a number, it fails:

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

Essentially, it is attempting something like this:

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

when it would need to be:

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

Applying that back to your code, it should be

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

which generates INSERT statements like this:

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