Re: Loading Constant Date

pcrock_at_lycos.com (Paul Crocker) wrote in message news:<960f9005.0208211214.a0679f0_at_posting.google.com>...
> Does anyone know how to load a constant date using Oracle8i SQL*Loader
> direct-path? have a date that includes a time element. Have tried
> "to_date('080102.13:15:27','mmddyy.hh24:mi:ss')", but that only works
> in conventional. Also have tried numerous permutations, including the
> "constant" keyword. No luck. Cannot use sysdate. Am aware that I
> can change the default NLS date format, but hoping there is a simple
> SQL*Loader solution. Has to be a common problem. An example of my
> control file is as follows:
>
> UNRECOVERABLE
> LOAD DATA
> truncate
> INTO TABLE cust
> SORTED INDEXES (idx1_cust)
> FIELDS TERMINATED BY '$'
> TRAILING NULLCOLS
> (
> CUST_NUM,
> MGR_NUM,
> AAVR_ID,
> ISSD_DT DATE "mm/dd/yyyy hh24:mi:ss",
> STATUS,
> NM,
> EXT_DT "to_date('080102.13:15:27','mmddyy.hh24:mi:ss')"
> )
>
> SQL*Loader: Release 8.1.7.4.0 - Production on Wed Aug 21 15:29:43 2002
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> SQL*Loader-417: SQL string (on column EXT_DT) not allowed in direct
> path.
>
> Thanks,
>
> Paul

Oracle confirms that since direct-path load bypasses the SQL engine, it is not possible to use sql functions like to_date, etc.

The solution is to change NLS_DATE_FORMAT to format you desire for the constant date. I added the NLS_LANG and NLS_DATE_FORMAT environment variables to my load script to override the init.ora settings. It worked fine.

Specific instructions from Oracle are:

Solution #1 (Conventional and Direct Path load):

 At the UNIX OS evironment level, set "NLS_LANG" and "NLS_DATE_FORMAT".      An individual user's date format can be modified by setting "NLS_LANG" and
 "NLS_DATE_FORMAT". Both parameters must be set. The "NLS_DATE_FORMAT"
 parameter does not take effect unless the "NLS_LANG" parameter is set.   

 Setting these parameters in the unix environment overrides the settings in the
 "init<sid>.ora" file.   

 The value for "NLS_LANG" should be the "NLS_LANGUAGE_NLS_TERRITORY" of the
 database.   

 To find these values use the following query:   

 SQL> select * from v$nls_parameters;   

 PARAMETER                 VALUE 
 ------------------------- ---------- 
 NLS_LANGUAGE              AMERICAN 
 NLS_TERRITORY             AMERICA 
 NLS_CURRENCY          $ 
 NLS_ISO_CURRENCY          AMERICA 
 NLS_NUMERIC_CHARACTERS    ., 
 NLS_DATE_FORMAT           DD-MON-YY 
 NLS_DATE_LANGUAGE         AMERICAN 
 NLS_CHARACTERSET          US7ASCII 
 NLS_SORT                  BINARY 

 The value of "NLS_LANGUAGE" is "AMERICAN" and "NLS_TERRITORY" is "AMERICA".
 "NLS_LANG" would be set to "AMERICAN_AMERICA.US7ASCII".   

 Using the appriopriate syntax for the current unix shell set the value for
 "NLS_LANG".     example:   

    setenv NLS_LANG AMERICAN_AMERICA.US7ASCII      

 Next set the "NLS_DATE_FORMAT" to the format of the date in the file to be
 loaded.   

 Example:   

    setenv NLS_DATE_FORMAT 'dd-mon-yy:hh:mi:ss'      

 You can now proceed with your load Received on Thu Aug 22 2002 - 17:29:37 CEST