sql loader how to escape double quotes

From: <boscardin.marco_at_gmail.com>
Date: Tue, 26 Aug 2008 08:03:27 -0700 (PDT)
Message-ID: <d9d7f1ca-4bfa-453b-8adc-c0fb577d13af@v13g2000pro.googlegroups.com>

Hi all,
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 Received on Tue Aug 26 2008 - 10:03:27 CDT