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;