如何在pl/sql解析器中添加所需列?

Question

下面的代码没有显示语法错误，但是在报告中出现了一个错误:

ORA-01722:无效号码

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

Answer 1

如果您使用的是Oracle 12，那么您可以尝试使用DEFAULT NULL ON CONVERSION ERROR选项显式地将值转换为数字:

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

如果工作，那么你知道c_phone_no或col002实际上不是一个数字，但可能是一个字符串，并且至少有一行字符串值不能解析为数字。

你可以使用:

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;

或:

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