4 Replies Latest reply: Jun 26, 2007 8:20 PM by 569664 RSS

    ORA-00933 using sqlldr and SYSDATE field

    user532159
      I am getting an ORA-00933 - "SQL command not properly ended" error when I try to set a field to the current date/time using the SYSDATE keyword. The following script works fine until I add the last line with SYSDATE. The field that I am trying to set with SYSDATE is defined in Oracle as TIMESTAMP(6).

      -------------------------------------------
      LOAD DATA
      INFILE 'R24.REGION.ERL.N1E200'
      BADFILE 'LOGS/FST_FRU_INFO_BADDATA.TXT'
      DISCARDFILE 'LOGS/FST_FRU_INFO_DISCARDDATA.TXT'
      REPLACE
      INTO TABLE TESTM8.FST_FRU_INFO
      FIELDS TERMINATED BY '~' OPTIONALLY ENCLOSED BY '"'
      (
      SPF_FRU_CD CHAR,
      SPG_SP_ID FILLER CHAR,
      SPG_SLA_INSRD_QTY FILLER INTEGER EXTERNAL,
      SPG_SLA_CLMNT_QTY FILLER INTEGER EXTERNAL,
      SPG_SLA_SUPPL_QTY FILLER INTEGER EXTERNAL,
      SPF_FRU_NM CHAR,
      SPF_TIME_ZONE_CD CHAR,
      SPF_DISP_ORDER_NBR DECIMAL EXTERNAL NULLIF SPF_DISP_ORDER_NBR = 'NULL',
      SPG_DIM_VALUE_QTY FILLER INTEGER EXTERNAL,
      SPG_PRO_INSP_QTY FILLER INTEGER EXTERNAL,
      SPG_PRO_SUPPL_QTY FILLER INTEGER EXTERNAL,
      SPG_PRO_FF_QTY FILLER INTEGER EXTERNAL,
      SPG_PRO_CUSTCP_QTY FILLER INTEGER EXTERNAL,
      SPG_PRO_TLOSS_QTY FILLER INTEGER EXTERNAL,
      SPF_MOBILE_ROLL_DT TIMESTAMP "yyyy-mm-dd" NULLIF SPF_MOBILE_ROLL_DT = 'NULL',
      SPF_CLAIMS_ROLL_DT TIMESTAMP "yyyy-mm-dd" NULLIF SPF_CLAIMS_ROLL_DT = 'NULL',
      SPF_SP_ID CONSTANT 'RELEASE1',
      SPF_WEEKEND_DISPATCH_IND CONSTANT 'Y',
      SPF_LAST_BATCH_START_TS SYSDATE
      )

      -------------------------------------------
        • 1. Re: ORA-00933 using sqlldr and SYSDATE field
          569664
          Hi,

          put the sysdate in double quotes.

          ei. SPF_LAST_BATCH_START_TS "SYSDATE"

          J
          • 2. Re: ORA-00933 using sqlldr and SYSDATE field
            121256
            SPF_LAST_BATCH_START_TS SYSDATE
            Please read about [url http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm#1008281]Setting a Column to an Expression Value in the manual.
            • 3. Re: ORA-00933 using sqlldr and SYSDATE field
              user532159
              Putting SYSDATE inside double quotes worked, as long as I also add a TRAILING NULLCOLS statement to the script (otherwise it generates errors).

              The documentation that I have says that SYSDATE is an SQL*Loader keyword that can be used to create a generated field, the same as RECNUM or CONSTANT, and as such I should be able to use it rather than having to use the double quotes to call the system sysdate function. But in any case, it's working now, so thanks for your help!
              • 4. Re: ORA-00933 using sqlldr and SYSDATE field
                569664
                >Putting SYSDATE inside double quotes worked, as long as I also add a TRAILING NULLCOLS statement to the script (otherwise it generates errors).


                I don't think TRAILING NULLCOLS has something to do with double quotes. The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. It clearly indicates that there are some records in your data file that do not have necessary columns to match up against the columns in your loader definition.