Solved

Sybase - BCP - oracle.

Posted on 2009-05-11
12
1,438 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now