[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

External table in Oracle 10g

Posted on 2006-11-16
13
Medium Priority
?
791 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
9 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 600 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 78

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:viani
ID: 17958659
I just resolved it by providing the date_mask for the dates
0
 
LVL 78

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 78

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 78

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

872 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