Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 26174 invoked from network); 17 Dec 2007 13:05:39 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 17 Dec 2007 13:05:39 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6CD5D7DAA0B; Mon, 17 Dec 2007 14:05:39 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17810-07; Mon, 17 Dec 2007 14:05:39 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CE11F7DA997; Mon, 17 Dec 2007 14:05:38 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 17 Dec 2007 13:18:13 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 910767DA49A for ; Mon, 17 Dec 2007 13:18:13 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10569-07 for ; Mon, 17 Dec 2007 13:18:13 -0500 (EST) Received: from outbound3-va3-R.bigfish.com (outbound-va3.frontbridge.com [216.32.180.16]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 655907DA498 for ; Mon, 17 Dec 2007 13:18:12 -0500 (EST) Received: from outbound3-va3.bigfish.com (localhost.localdomain [127.0.0.1]) by outbound3-va3-R.bigfish.com (Postfix) with ESMTP id 778686EA93A; Mon, 17 Dec 2007 18:18:12 +0000 (UTC) Received: from mail173-va3-R.bigfish.com (si1-va3 [10.7.14.5]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by outbound3-va3.bigfish.com (Postfix) with ESMTP id 75CFC1380070; Mon, 17 Dec 2007 18:18:12 +0000 (UTC) Received: from mail173-va3 (localhost.localdomain [127.0.0.1]) by mail173-va3-R.bigfish.com (Postfix) with ESMTP id 5F84F10582AB; Mon, 17 Dec 2007 18:18:12 +0000 (UTC) X-BigFish: VP X-MS-Exchange-Organization-Antispam-Report: OrigIP: 198.22.236.82;Service: EHS Received: by mail173-va3 (MessageSwitch) id 1197915492374305_2394; Mon, 17 Dec 2007 18:18:12 +0000 (UCT) Received: from mailb.nysemail.state.ny.us (mailb.nysemail.state.ny.us [198.22.236.82]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by mail173-va3.bigfish.com (Postfix) with ESMTP id 51AB61048077; Mon, 17 Dec 2007 18:18:12 +0000 (UTC) Received: from EXCNYSM0A1AJ.nysemail.nyenet ([10.66.81.139]) by mailb.nysemail.state.ny.us with Microsoft SMTPSVC(6.0.3790.2499); Mon, 17 Dec 2007 13:18:11 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: sql loader question Date: Mon, 17 Dec 2007 13:18:11 -0500 Message-ID: In-Reply-To: <121720071713.26183.4766AE410004A047000066472200734840079D9A00000E09A1020E979D@comcast.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: sql loader question From: "Mercadante, Thomas F (LABOR)" To: , X-OriginalArrivalTime: 17 Dec 2007 18:18:11.0923 (UTC) FILETIME=[2EEA0A30:01C840D9] X-archive-position: 3978 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-to: Thomas.Mercadante@labor.state.ny.us List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Ryan, Have you tried the following? my_date "to_date(nvl(:my_date,'9999-01-01'),'YYYY-MM-DD')" Tom -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of ryan_gaffuri@comcast.net Sent: Monday, December 17, 2007 12:14 PM To: oracle-l@freelists.org Subject: sql loader question I rarely use sql loader. I need to account for nulls and set them to January 1, 9999 I am getting errors when I try to use a to_date function in sql loader even though I have seen examples just like this on the web. This is 10.2 my_date date "to_date(:my_date,'YYYY-MM-DD')" I get: ORA-01821: date format not recognized The following works: my_date date "YYYY-MM-DD" I am trying to get to something like this: my_date date "decode(:my_date,null,to_date('9999-01-01','YYYY-MM-DD'),to_date(:my_dat e,'YYYY-MM-DD')" I dont think nvl will give me what i want since I still need an "else" mask. At this point, I can't get the basic syntax right. Here is the header part of the control file LOAD DATA CHARACTERSET UTF8 INFILE "load.data" "var 7" APPEND INTO TABLE my_table FIELDS TERMINATED BY ' ' ENCLOSED By "'" AND "'" -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l