Solved

External table in Oracle 10g

Posted on 2006-11-16
13
720 Views
Last Modified: 2008-02-01
Hi I used the following to load data.
CREATE TABLE DATA_LOAD_CTR
(
  fields list)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY LSHR_LOAD_DIR
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE
         FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "'"
                MISSING FIELD VALUES ARE NULL
        )
     LOCATION (LSHR_LOAD_DIR:'data_load.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
The above script works fine in development database and inserts about 14000 record. But the same inserts only one record in the staging database. It does not show any error. Why would it insert only one record from the flat file. Have I to mention table space here? If so how can I do that for this table.
Thanks.
0
Comment
Question by:viani
  • 5
  • 4
13 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 150 total points
ID: 17957945
you might try adding the discard, bad and logfile parameters:

BADFILE 'ulcase1.bad'
DISCARDFILE 'ulcase1.dis'
LOGFILE 'ulcase1.log'


hopefully one of those files will tell you why.
0
 

Author Comment

by:viani
ID: 17958127
yes it says 'error procesiing start_date' not a valid month. The format in the input flat file is 'mm/dd/yyyy'. Do I have to make it same in table too? If so, how?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 17958315
You'll need to add the date format and specify each of the fields.

Check out:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:8128892010789
0
 

Author Comment

by:viani
ID: 17958659
I just resolved it by providing the date_mask for the dates
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18364989
Have to disagree with the recommendation.  Adding the date mask is what my last post suggested and I believe it was my first post that uncovered the error.
0
 

Author Comment

by:viani
ID: 18365658
Hi slightwv:
I'm not trying to be mean. It could be hard to believe, but I applied the format mask before I read your solution. However, if you insist, then you may take the points.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18366105
Don't I even get credit for giving you the solution to locate the error in the first place?
0
 

Author Comment

by:viani
ID: 18366306
Your very first post was about adding the files bad, discard, etc. When I mentioned to you that the error is 'error procesiing start_date", I started applying the date mask. In the mean time you told me the same solution so, I think that we could split the points.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19209362
I have no problem with the askers recommendation of reducing points.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2003 Query 2 51
oracle query help 18 99
Remote Server returned '400 4.4.7 Message delayed' 3 83
Exchange 2010 Database wont mount 3 28
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now