[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to load data with escaped characters in SQL*Loader

Posted on 2001-08-31
7
Medium Priority
?
856 Views
Last Modified: 2012-06-27
I want to load data dumped by Informix unload command. If the field delimiter or a newline is found in values, Informix unload will use escape '\'. How do I load it to Oracle? Does SQL*Loader recognize escaped characters? What other tools may I use?

For example, the following data should be one record in Oracle. Thanks.

1|this is a test\
 with newline in values
0
Comment
Question by:hakula
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:jkstill
ID: 6446140
I believe best choice is to clean up the data before loading it into Oracle.

A small Perl script is the perfect method for doing this.  Trying to load
this directly through SQL*Loader may not be possible.


0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6446168
Hi hakula
Is the record delimiter is also the new line char

Sam..
0
 
LVL 1

Author Comment

by:hakula
ID: 6446683
Hi Sam,

In the example, the delimiter is '|'. The newline is in the middle of data for the second column.

Thanks.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 3

Expert Comment

by:Wadhwa
ID: 6446879
Hi hakula,

I am asking not the field delimiter , i am asking about record delimiter

Waht is the delimeter between one record and the second record.

Sam..
0
 
LVL 1

Author Comment

by:hakula
ID: 6447598
Sam,

yes, record delimiter is newline.
Thanks.
0
 
LVL 3

Accepted Solution

by:
Wadhwa earned 200 total points
ID: 6447723
Hi hakula,
since The newline is in the middle of data for the second column and record delimiter is also the new line char , in this case it is not possible to load data  directly through sql*loader. You have make changes in the text  file to change the record delimiter  to some other char other then  "|" or "new line char".

Here you may find  some tool to load the data, you may try it. http://www.benthicsoftware.com/products.html
The name of the tool is GOLDLOAD


Goodluck
Sam
0
 
LVL 1

Author Comment

by:hakula
ID: 6449456
Sam,

Thanks for your idea. Changing the record delimiter is the key to the problem, but your comment failed to mention how to change it. I figured out how to change the record delimiter myself.

Thanks again.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

649 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