Solved

Oracle 10g SQL Loader Using TO_date need to set value to NULL if date is blank

Posted on 2008-10-13
10
7,534 Views
Last Modified: 2013-12-18
Oracle 10g SQL Loader with comma delimited file.  I can get the TO_DATE to work but I need to also incorporate NULL if the field is blank.  I saw examples on this site but when I tried it in the comma delimited file.  It fails.  
D_UPD        "TO_DATE(nullif(D_UPD,BLANKS),'MM/DD/YYYY HH24:MI')"
 
Works if I remove the nullif to handle blanks but some of my records have blank dates so I want to replace it will nulls
 
 
 
Record 1: Rejected - Error on table ILAP_026, column D_UPD.
ORA-00984: column not allowed here

Open in new window

0
Comment
Question by:pattisp1
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22706517

Try this:

-- etc --
D_UPD   DATE 'MM/DD/YYYY HH24:MI' NULLIF D_UDT = BLANKS,
-- etc --

Open in new window

0
 
LVL 1

Accepted Solution

by:
asksigh earned 250 total points
ID: 22709319
Hey here,

I have used something very similar to Mike in the past.
...
 D_UPD                     DATE "MM/DD/YYYY HH24:MI" NULLIF (D_UPD=BLANKS),
...

Hope it helps.
Cheers
Asksigh
0
 

Author Comment

by:pattisp1
ID: 22711801
Thank you!!  Testing right now!!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pattisp1
ID: 22711915
These options didn't work for me.  I get the following error:
Record 1: Rejected - Error on table ILAP_026, column D_UPD.
ORA-01858: a non-numeric character was found where a numeric was expected

I think I left an important requirement out of my first question.  I believe I have to use the TO_DATE because my date field in the comma delimited file is of the format MM/DD/YYY HH24:MI and I want to load it into Oracle as a DATE type field.   Please let me know if this thinking is correct.

Thank you!!!
0
 
LVL 32

Expert Comment

by:awking00
ID: 22713463
D_UPD "DECODE(TRIM(:D_UPD),NULL,NULL,TO_DATE(:D_UPD,'MM/DD/YYYY HH24:MI'))"
0
 

Author Comment

by:pattisp1
ID: 22723753
I tried this solution but still I'm getting the error that a numeric was expected.

D_UPD                                NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "DECODE(TRIM(:D_UPD),NULL,NULL,TO_DATE(:D_UPD,'MM/DD/YYYY HH24:MI'))"
SSA_RIC                              NEXT     *   ,  O(") CHARACTER            
DON                                  NEXT     *   ,  O(") CHARACTER            
DON_QTY                              NEXT     *   ,  O(") CHARACTER            
DON_STAT                             NEXT     *   ,  O(") CHARACTER            
DON_DATE                             NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:DON_DATE, 'MM/DD/YYYY HH24:MI')"
DON_IPD                              NEXT     *   ,  O(") CHARACTER            
DON_ESD                              NEXT     *   ,  O(") CHARACTER            
    NULL if DON_ESD = BLANKS
ARFORGEN_IND                         NEXT     *   ,  O(") CHARACTER            
ITV_QTY                              NEXT     *   ,  O(") CHARACTER            
SHIP_TO                              NEXT     *   ,  O(") CHARACTER            
SHIP_FROM                            NEXT     *   ,  O(") CHARACTER            
LAST_STATUS                          NEXT     *   ,  O(") CHARACTER            
TAG_ID                               NEXT     *   ,  O(") CHARACTER            
D_TAG                                NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:D_TAG,'MM/DD/YYYY HH24:MI')"
CUMUL_AGE                            NEXT     *   ,  O(") CHARACTER            
D_ARRIVAL                            NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:D_ARRIVAL,'MM/DD/YYYY HH24:MI')"
LAST_KNOWN_LOCATION                  NEXT     *   ,  O(") CHARACTER            
ILAP_ID                                                   SEQUENCE (MAX, 1)

value used for ROWS parameter changed from 64 to 17
Record 1: Rejected - Error on table ILAP_026, column D_UPD.
ORA-01858: a non-numeric character was found where a numeric was expected


Table :
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22724077

This should have worked:
D_UPD   DATE 'MM/DD/YYYY HH24:MI' NULLIF D_UDT = BLANKS.

Post a sample of your data:

0
 
LVL 32

Expert Comment

by:awking00
ID: 22725156
Then you must have alpha or special characters in your data. If you're in a test environment, try loading the date fields into varchar datatypes then select where translate(datefield,'#0123456789/','#') is not null to find the problem data.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22727736

Another issue may be that you have to code your control file fields to EXACTLY match the order of the source file fields!

Check it out...
0
 
LVL 1

Expert Comment

by:asksigh
ID: 22765117
Cheers - glad i could help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question