Solved

External table in Oracle 10g

Posted on 2006-11-16
13
733 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:viani
ID: 17958659
I just resolved it by providing the date_mask for the dates
0
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display multiple images in report 12 118
powershell and sql server - alerting 7 87
C# Application Local DB Connection String 23 177
DBF to ... Converter 5 71
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…

778 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