OraFAQ Forum: Server Utilities » Reading data fom file using External Loader

Home » RDBMS Server » Server Utilities » Reading data fom file using External Loader (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reading data fom file using External Loader [message #672933]

Thu, 01 November 2018 06:42

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear Guru,

I have an External loader table which read from .txt file, It has 106 column and one of the column whose length is 500 byte has data in multiple line whcich is causing the ETL to fail as it reject the rows and reaches it limit. There is no double quote around the data.

What can be done so that the records will not spill to other feild or other rows and can be read correctly.

The text file is generate using BCP utility in SQL server so i am not sure how to put double quote around the records

Thanks

Report message to a moderator

Re: Reading data fom file using External Loader [message #672943 is a reply to message #672933]

Thu, 01 November 2018 07:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

guddu_12 wrote on Thu, 01 November 2018 04:42

Dear Guru,

I have an External loader table which read from .txt file, It has 106 column and one of the column whose length is 500 byte has data in multiple line whcich is causing the ETL to fail as it reject the rows and reaches it limit. There is no double quote around the data.

What can be done so that the records will not spill to other feild or other rows and can be read correctly.

The text file is generate using BCP utility in SQL server so i am not sure how to put double quote around the records

Thanks

Oracle is the victim; not the culprit.
How is Oracle to know & decide where the correct start and end of this column for this 500 byte column?
If ETL producer does not provide the double quotes as required,
then you need to write a small preprocessor utility that adds them correctly

Report message to a moderator

Re: Reading data fom file using External Loader [message #672952 is a reply to message #672943]

Thu, 01 November 2018 08:02

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

File is Pipe delimited so we know when next column value start

Report message to a moderator

Re: Reading data fom file using External Loader [message #672962 is a reply to message #672952]

Thu, 01 November 2018 08:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

guddu_12 wrote on Thu, 01 November 2018 06:02

File is Pipe delimited so we know when next column value start

So direct Oracle to use the same pipe delimiter

Report message to a moderator

Re: Reading data fom file using External Loader [message #672968 is a reply to message #672962]

Thu, 01 November 2018 08:49

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

But column value is getting splitted into multiple line that cause ETL to Fail. Is there any work around to Read Splitted column values into multiple line from the file

Report message to a moderator

Re: Reading data fom file using External Loader [message #672973 is a reply to message #672968]

Thu, 01 November 2018 08:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

guddu_12 wrote on Thu, 01 November 2018 06:49

But column value is getting splitted into multiple line that cause ETL to Fail. Is there any work around to Read Splitted column values into multiple line from the file

The EXTERNAL FILE definition needs to be changed/corrected.

Report message to a moderator

Re: Reading data fom file using External Loader [message #673147 is a reply to message #672952]

Thu, 08 November 2018 23:02

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Here is an example for you. I am using Windows so I used RECORDS DELIMITED BY '|\r\n'. If you are using Unix it would be RECORDS DELIMITED BY '|\n'. You must also make sure that you specify a large enough maximum size such as CHAR(550) if the field length is greater than the default of 255 bytes.

SCOTT@orcl_12.1.0.2.0> HOST TYPE test.dat
1|azzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzz
byyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyy|
2|cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxx
dwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
wwwwwwwwww|
3|evvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvv
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
uuuuuuuuuu|


SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
  2    (col1  NUMBER,
  3     col2  VARCHAR2(550))
  4  ORGANIZATION external
  5    (TYPE oracle_loader
  6     DEFAULT DIRECTORY my_dir
  7     ACCESS PARAMETERS
  8       (RECORDS DELIMITED BY '|\r\n'
  9        LOGFILE 'test.log'
 10        FIELDS TERMINATED BY "|" LDRTRIM
 11        REJECT ROWS WITH ALL NULL FIELDS
 12          (col1,
 13           col2  CHAR(550)))
 14     LOCATION ('test.dat'))
 15  REJECT LIMIT UNLIMITED
 16  /

Table created.

SCOTT@orcl_12.1.0.2.0> select * from test_tab
  2  /

      COL1
----------
COL2
--------------------------------------------------------------------------------
         1
azzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

zzzzzzzzzzzz
byyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

yyyyyyyyyy

         2
cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxx
dwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww

wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww

wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww

wwwwwwwwww

         3
evvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

vvvvvvvvvvvv
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
 uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
u
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu

uuuuuuuuuu


3 rows selected.

Report message to a moderator

Previous Topic:	Passing parameter to control file in SQLLDR (SQL LOADER)
Next Topic:	Export job finishing time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 15 03:03:35 CST 2026