sql loader how to escape double quotes
boscard...@gmail.com
I would like to know how it is possible to escape double quotes in a
format string in the control file. I explain the problem.
I have a table with 2 columns
CREATE TABLE MARCO
(
TYPE VARCHAR2(3 BYTE) NULL,
START TIMESTAMP(6) NOT NULL
)
in the data file there is something like
DET2008-29-12T12:00:35.0+0200
in sqlplus the following command is working perfectly
SQL> select to_date('2008-29-12T12:00:35.0+0200', 'YYYY-DD-
MM"T"HH24:MI:SS."FF1""+""TZH""TZM"') from dual;
TO_DATE('2008-2
---------------
29-DEC-08
SQL>
but if if put it into the control file this way:
LOAD DATA
APPEND INTO TABLE MARCO
WHEN (01) = 'DET'
(
RECORD_TYPE POSITION (1:3 ) CHAR,
START_SESSION POSITION (4:29)
"{to_timestamp(:START_SESSION, 'YYYY-DD-
MM"T"HH24:MI:SS."FF1""+""TZH""TZM"')}"
)
I got the error
SQL*Loader-350: Syntax error at line 6.
Expecting valid column specification, "," or ")", found "T".
4:29) "{to_timestamp(:START_SESSION, 'YYYY-DD-MM"T"HH24:MI:SS."FF1""+"
since it is interpreting the double quotes before the T as the closing
one for the column definition. I tried replacing the double quotes in
the format string with 2 single quotes or with \" but then I always
get the same error
(137) - SQL*Loader failed to load one or more records
(and in the log file): 1 Row not loaded due to data errors.
(by the way, if I change data type in both table and control file to
varchar2 everything is working fine).
Would someone have any idea how to solve this problem? Looks pretty
silly but it's blocking :(
Thanks for the help,
Marco