Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 6180 invoked from network); 17 Dec 2007 17:22:17 -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 17:22:17 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4AD407D9F32; Mon, 17 Dec 2007 18:22:17 -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 24781-09; Mon, 17 Dec 2007 18:22:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BB1787D9E6E; Mon, 17 Dec 2007 18:22:16 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 17 Dec 2007 17:34:51 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A43137DA5D0 for ; Mon, 17 Dec 2007 17:34:51 -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 15644-08 for ; Mon, 17 Dec 2007 17:34:51 -0500 (EST) Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.190]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 45B077DA5C1 for ; Mon, 17 Dec 2007 17:34:50 -0500 (EST) Received: by mu-out-0910.google.com with SMTP id i2so4208418mue.2 for ; Mon, 17 Dec 2007 14:34:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:from; bh=I8oBi5i+d/wjobGWPWHRn2r4QjJfXc04dyP9xjvUuY4=; b=hIzcDHRnIGkklXZ3qClzwsJSIpB5gUaXdjmAZKOXVnLYuodVzWpiMRSxFKAjK4FvEIQvr6hUIV5EXHONY58guMT9xFso2wmX+x49nwAsk54FS0BhHtJyF5ak1wOAC5mcWBUi35mrUP8OXjjnC0OuyU3aR8bfvo2sPn/KUO/4fpo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:from; b=YMUMHcfyUQ2/otCjRMtlV3QdN2x3oZTgLqMVNYqg5QRvkstB/iPEUYH/t8QsvF6QPaRT/cjq/XzBq8vvG7ka8BUfXErDvbJF6jlEtKIlJC/EI3SaY4GEFif4ea9l73nAaVMsZuJuQlzq0AxcnLh+fwrtDeIH/6QMXmeaelBuU2o= Received: by 10.86.1.1 with SMTP id 1mr6955460fga.2.1197930890023; Mon, 17 Dec 2007 14:34:50 -0800 (PST) Received: from ?192.168.1.10? ( [87.163.75.2]) by mx.google.com with ESMTPS id k29sm18286584fkk.2007.12.17.14.34.48 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 17 Dec 2007 14:34:49 -0800 (PST) Message-ID: <4766F985.5050305@googlemail.com> Date: Mon, 17 Dec 2007 23:34:45 +0100 User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: ryan_gaffuri@comcast.net CC: oracle-l@freelists.org Subject: Re: sql loader question References: <121720071713.26183.4766AE410004A047000066472200734840079D9A00000E09A1020E979D@comcast.net> In-Reply-To: <121720071713.26183.4766AE410004A047000066472200734840079D9A00000E09A1020E979D@comcast.net> Content-Type: text/plain; charset=ISO-8859-15; format=flowed From: Maxim Demenko X-archive-position: 3984 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mdemenko@gmail.com Precedence: normal Reply-to: mdemenko@gmail.com 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_gaffuri@comcast.net schrieb: > 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 > > my_date date "decode(:my_date,null,to_date('9999-01-01','YYYY-MM-DD'),to_date(:my_date,'YYYY-MM-DD')" > > > Well, this behaviour is documented: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1244 Small example: SQL> !cat a.ctl LOAD DATA INFILE a.dat TRUNCATE INTO TABLE A FIELDS TERMINATED BY ',' TRAILING NULLCOLS (id integer external, dt date "YYYY-MM-DD" "decode(:dt,null,'9999-01-01',:dt)", id1 integer external) SQL> !sqlldr userid=scott/tiger control=a.ctl log=a.log SQL*Loader: Release 10.2.0.3.0 - Production on Mon Dec 17 23:29:26 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 2 SQL> select * from a; ID DT ID1 ---------- ------------------- ---------- 1 15.10.1965 00:00:00 3 2 01.01.9999 00:00:00 4 Of course, nvl seems to be perfectly suitable as well. Best regards Maxim -- http://www.freelists.org/webpage/oracle-l