Solved

External table in Oracle 10g

Posted on 2006-11-16
13
764 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
13 Comments
 
LVL 77

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 77

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

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

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 77

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 77

Expert Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

718 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