We help IT Professionals succeed at work.
Get Started

Loading data that contains newline characters into external table

gatorvip
gatorvip asked
on
3,073 Views
Last Modified: 2013-12-18
I'm loading a flat file with fixed-width fields into an 'external table' containing clobs. The file comes from a Unix environment and the db I'm loading it in is Oracle 10g on Windows (newline chars are different). The records are delimited by newline characters. The problem is that if a newline character occurs within a field, the loader does not interpret it correctly.

Here's the relevant table definition
....
ORGANIZATION EXTERNAL
(
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_DIR_TABLES"
      ACCESS PARAMETERS
      (
            RECORDS DELIMITED BY NEWLINE
            BADFILE EXT_DIR_BAD:'EXT_Z.bad'
            LOGFILE EXT_DIR_LOG:'EXT_Z.log'
            DISCARDFILE EXT_DIR_DSC:'EXT_Z.dsc'
            FIELDS
            LRTRIM
            MISSING FIELD VALUES ARE NULL
            REJECT ROWS WITH ALL NULL FIELDS
            (
                  "ID"  POSITION(1:11) decimal external(11),
                  ....
                  "DOS" POSITION(301:10300) char(10000)   -- works fine if no newlines
            ).....

How do I specify that newline characters in that field should be treated as any other character?
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 2 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE