In the process of importing data which can be irregular from a flat file it is convenient to import all values as character strings, in this case Oracle’s varchar2, and then cast them into the proper format using the built-in datatype converters as e.g. to_number. The problem occurs when some value which is not convertible is stored in the column. This could be either because table column-overflow in the import process or because the value is optional and may be null or other non-set value. In this case the import methods doesn’t work.

Here are two methods I find useful for solving these problems, resulting in two PL/SQL functions embedding the functions used by oracle, but with a controlled exception where null values are stored and the error logged. Working on tables with several million rows I find this approach more useful than halting the process.

Safe number converter

CREATE OR REPLACE FUNCTION
safe_to_number (p varchar2)
RETURN number
IS
retval number;

BEGIN
IF p IS NULL THEN
retval := null;
ELSE
retval := to_number(p);
END IF;
return retval;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not change value to number');
retval := null;
return retval;

END;

Safe timestamp converter

create or replace function safe_to_timestamp
( p_str in varchar2
, p_mask in varchar2)
return timestamp
is
d date;
begin
d := to_timestamp(p_str, p_mask);
return d;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Could not change value to timestamp');
return null;
end;

I use these after the tables are imported as straight string-tables, to create a copy, but with the correct data format.

CREATE TABLE formatted_table as
SELECT
safe_to_timestamp(playdatetime,'YYYY-MM-DD HH24.MI.SS.FF') playdatetime,
safe_to_timestamp(dateval,'YYYY-MM-DD HH24.MI.SS.FF') dateval,
safe_to_number(id_table) id_table
FROM unformatted_table;