Home » SQL & PL/SQL » SQL & PL/SQL » Creating oracle table from external files (oracle 9i)
Creating oracle table from external files [message #411105] Thu, 02 July 2009 00:00 Go to next message
dd_1
Messages: 1
Registered: July 2009
Junior Member
Hello teachers,

I an trying to create a table from external file statemnet i use is as

CREATE TABLE ref_fl (
niin CHAR(20),
ncage CHAR(20),
referencenumber CHAR(20),
rncc CHAR(2),
rnvc CHAR(2),
dac CHAR(2),
rnsc CHAR(2),
rnacc CHAR(2),
rnjc CHAR(2),
locallyadded CHAR(2),
cleansedreference CHAR(2))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE 'bad_%a_%p.bad'
LOGFILE 'log_%a_%p.log'
READSIZE 20971520
FIELDS TERMINATED BY '\t'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(niin, ncage, referencenumber, rncc,rnvc,dac,rnsc,rnacc,rnjc,locallyadded,cleansedreference))
LOCATION ('rd.txt)
)
PARALLEL
REJECT LIMIT 0;


Problem is the file rd.txt is created from java. ?It has almost 2000 lines. But when I use the above stamenet it just reads the first line and store in datatabse(no error thrown).

IF I manually create rd.txt file (with same data) oracle statement reads all the lines.Can't figure out the problem is how do I solve the problem?
Re: Creating oracle table from external files [message #411109 is a reply to message #411105] Thu, 02 July 2009 00:20 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.
Have a look at your log and bad files. By the way, I don't know if your file name formats are valid.

As you have "REJECT LIMIT 0", it is likely you have an error at second line.

If your file is not created in the same OS as the db server, then take care to ftp in binary mode or change your RECORDS DELIMITED clause.

Regards
Michel
Previous Topic: Internationalization
Next Topic: Tracing Updates
Goto Forum:
  


Current Time: Thu Dec 08 22:16:44 CST 2016

Total time taken to generate the page: 0.08806 seconds