Solved

External table in Oracle 10g

Posted on 2006-11-16
13
744 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Remote Server returned '400 4.4.7 Message delayed' 3 449
Unrecognized Database Format 8 122
best datatype for oracle table email creation 8 58
Very Large data in MYSQL 7 75
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
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…

821 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