Solved

Sybase - BCP - oracle.

Posted on 2009-05-11
12
1,465 Views
Last Modified: 2012-05-06
I am extracting data from sybase using bcp tool ( building pipe delimited data file ) & there by loading into oracle database.

Issue: some fields are having newline characters and it is making the load in correct.

Is there a way to remove the newline character while pulling the data through BCP.....can anything be done at the format file....??


0
Comment
Question by:vishali_vishu
12 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 24354358
The way around this is to choose a different Line Terminator character on your BCP out and SQL*Loader in.  If necessary, choose a non-printable character that both utilities can handle.  That way you preserve the integrity of the data.

Regards,
Bill
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 24354684
is it possible to do something at the format file level on a particular column ?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355048
I agree with grant300's suggestion, a couple of comments:

1st approach, as grant300 suggests, however, I don't know BCP so he must help here with the Sybase part.
Make sure the text column is enclosed by quotes, then you can also use the stream format in sql loader, as grant suggests, using a combination of characters as the line terminator.

This uses |\n as terminator in the SQL Loader control file and use stream format.

LOAD DATA
INFILE 'foo.dat' "str '|\n'"
TRUNCATE INTO TABLE foo
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
... your field defs go here
)


2nd approach:
Or.. run a SQL query on sybase replacing all occurences of \n with something like "<SLASH_N>"
Extract with BCP
Load normally with SQL Loader
Run Oracle update, replacing <SLASH_N> with '\n' again

0
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.

 
LVL 1

Author Comment

by:vishali_vishu
ID: 24355147
i thought of doing like this....


instead of \n as line terminator...i will be using <eol>.

Now i get the data file with single line ( line breaks only when there is a  \n in column values )...

Now use sed to replace \n with \t (tab).....

and use sed to replace <eol> with \n....

now i will get the data file with \n as the line terminator....


-----------------------------------------

but my question is ....... can we get it done at the format file level ....

something like.... replace ( newline with space or tab on a particular column)...?

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355232
Try this:

In Sybase:

update tbl set text_col = replace(text_col, char(10), '<eol>');

In Oracle:

update tbl set text_col = replace(text_col, '<eol>', chr(10));
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355241
Note in Sybase its char() vs Oracle chr()
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 24355516
i just have read access to sybase.... and i am supposed to use BCP to pull the data and load to oracle...

I can't update the tables.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355573
I do not know BCP, so I will defer to grant300 on that part.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 24359738
To answer OP's question:

You can't use a BCP format file to do any updates to the data. The format file specifies the format and says nothing about the content (other than its datatype).

You could however create a view on the underlying data that does some transformations, and the BCP out of the view.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24367438
@vishali_vishu

A more equitable point split would probably have been to include grant300's initial advice that led to the final solution, so I feel like accepting the comment that you selected is not a proper closure for this question. When closing a question, please consider the comments in the thread that actually helped you with the solution, not just the final one. Thanks.
0
 
LVL 1

Author Comment

by:vishali_vishu
ID: 24369247
mrjoltcola: sorry for that..... how to split the points ?


0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24369312
Next time you close a question, look for a button that says "Accept multiple solutions" and it will allow you to select the ones you want.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

839 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