How to add desired column in pl/sql parser?-CodePudding

The following code shows no syntax error but in report it rise an error which is:

ORA-01722: invalid number

select line_number, col002 , case when exists (select null from cdr_personal_info c where c.phone_no=col002 ) then 'Yes' else null end as cdr
  from apex_application_temp_files f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  p_xlsx_sheet_name             => :P31_XLSX_WORKSHEET,
                  p_max_rows                    => 500,
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => f.filename ) ) p
 where f.name = :P31_FILE

CodePudding user response：

If you are on Oracle 12, then you can try explicitly converting the values to a number with the DEFAULT NULL ON CONVERSION ERROR option:

select line_number,
       col002,
       case
       when exists (select null
                      from cdr_personal_info c
                     where TO_NUMBER(c.phone_no DEFAULT NULL ON CONVERSION ERROR)
                           = TO_NUMBER(col002 DEFAULT NULL ON CONVERSION ERROR)
                   )
       then 'Yes'
       else null
       end as cdr
  from apex_application_temp_files f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  p_xlsx_sheet_name             => :P31_XLSX_WORKSHEET,
                  p_max_rows                    => 500,
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => f.filename ) ) p
 where f.name = :P31_FILE

If that works then you know that either c_phone_no or col002 is not actually a number but is probably a string and there is at least one row where the string value cannot be parsed as a number.

You can then find it using:

select line_number,
       col002
  from apex_application_temp_files f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  p_xlsx_sheet_name             => :P31_XLSX_WORKSHEET,
                  p_max_rows                    => 500,
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => f.filename ) ) p
 where f.name = :P31_FILE
   and TO_NUMBER(col002 DEFAULT NULL ON CONVERSION ERROR) IS NULL;

or:

select *
  from cdr_personal_info c
 where TO_NUMBER(c.phone_no DEFAULT NULL ON CONVERSION ERROR) IS NULL;