RE: SQL Server To Oracle

From: Scott Canaan <srcdco_at_rit.edu>
Date: Fri, 11 Mar 2022 14:23:22 +0000
Message-ID: <17fc6bfbbc314c5fb5eda622205bc2c7_at_ex04mail02a.ad.rit.edu>

Tried this. If the SQL Server text field has line breaks in it, it is in the file that way, which is still the issue I'm having. SQL*Loader can't deal with that.

I've told the customer that I can't get that data based on the tools I have available. This is a one-time load, not something that will be done repeatedly, so I can't spend any money on it.

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: Sweetser, Joe <JSweetser_at_icat.com> Sent: Thursday, March 10, 2022 2:25 PM
To: Sweetser, Joe <JSweetser_at_icat.com>; mwf_at_rsiz.com; Scott Canaan <srcdco_at_rit.edu>; oracle-l_at_freelists.org Subject: RE: SQL Server To Oracle

Can't find my notes but just looked and something like this may work...SQL version I see is 13.0.6300.2. Not sure what that equates to in 20xx notation.

In SSMS...

Right click on database > Tasks > Export Data
Choose data source (I used SQL Server Native Client 11.0), click Next
Choose Flat File Destination; enter a filename; Format = Delimited, click Next
Specify table/view/query depending on what you need; click Next
Next screen for me is "Configure Flat File Destination" and it allows me to choose both Row and Column delimiters
Columns choices I see are comma, tab, vertical bar, semi-colon, colon, {CR}{LF}, {CR}, {LF}

Good luck,
-joe

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Sweetser, Joe Sent: Thursday, March 10, 2022 12:05 PM
To: mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>; 'Scott Canaan' <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: SQL Server To Oracle

� Did you try using the gui wizard? I *thought* there was a chance to specify the delimiter and so forth. Again, I'm really rusty on this, so bring your salt shaker.

I agree. I am 99% sure I have done that and used the vertical bar as a delimiter because commas were embedded in the source data. I will try to find any notes I have and forward to the list.

-joe

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Mark W. Farnham Sent: Thursday, March 10, 2022 12:00 PM
To: 'Scott Canaan' <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: SQL Server To Oracle

Did you try using the gui wizard? I *thought* there was a chance to specify the delimiter and so forth. Again, I'm really rusty on this, so bring your salt shaker.

If you can connect network wise to the source data that might also be an option to select directly into your Oracle table. That worked from Sybase to Oracle circa sql*net one, but only if the line length total was under about 512. If it was longer you had to craft the query to have embedded line continuation characters followed by enough white space to make sure you got a new line after each continuation character. It was could be very tedious and you needed to figure out the maximum column length for each column to get the wrapping correct and I think there was some problem with the difference between zero length text values and nulls.

Some of those brain cells encased in rust are resisting waking up completely, so bring a salt shaker.

One other idea is to blend output files, one that fits (or more than one if they won't) all the non-text columns, an one each for the text columns prefaced by unique identifier. Then you can use text a script text processor on Linux with a pretty good chance to get it into one line.

mwf

From: Scott Canaan [mailto:srcdco_at_rit.edu] Sent: Thursday, March 10, 2022 1:09 PM
To: mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: SQL Server To Oracle

Flat file only allows for tab delimited. It still does the same thing, create a new row for each CR, which SQL*Loader doesn't like.

Scott Canaan '88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Mark W. Farnham Sent: Thursday, March 10, 2022 12:24 PM
To: Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: SQL Server To Oracle

Did you already try using flat file output option and choosing comma as your delimiter?

It has been a long time for me (since before MS bought sql server), so of course things may have changed.

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Scott Canaan Sent: Thursday, March 10, 2022 12:02 PM
To: 'oracle-l_at_freelists.org'
Subject: SQL Server To Oracle

I'm trying to get some data from a SQL Server 2012 database into an Oracle 19c database on Linux. I'm having trouble with the CLOB fields (text in SQL Server). When I export the data to a csv, it splits the data in that is larger into several rows, which SQL*Loader doesn't like. I don't know how to get the data exported so I can use it or to import the data as it is. If anyone has any suggestions, I'd really appreciate it.

Thank you,

This e-mail transmission and any attachments that accompany it may contain information that is privileged, confidential or otherwise exempt from disclosure under applicable law and is intended solely for the use of the individual's to whom it was intended to be addressed. If you have received this e-mail by mistake, or you are not the intended recipient, any disclosure, dissemination, distribution, copying or other use or retention of this communication or its substance is prohibited. If you have received this communication in error, please immediately reply to the author via e-mail that you received this message by mistake and also permanently delete the original and all copies of this e-mail and any attachments from your computer. Please note that coverage cannot be bound or altered by sending an email. You must receive written confirmation from a representative of our firm to put coverage in force or make changes to an existing policy. This e-mail transmission and any attachments that accompany it may contain information that is privileged, confidential or otherwise exempt from disclosure under applicable law and is intended solely for the use of the individual's to whom it was intended to be addressed. If you have received this e-mail by mistake, or you are not the intended recipient, any disclosure, dissemination, distribution, copying or other use or retention of this communication or its substance is prohibited. If you have received this communication in error, please immediately reply to the author via e-mail that you received this message by mistake and also permanently delete the original and all copies of this e-mail and any attachments from your computer. Please note that coverage cannot be bound or altered by sending an email. You must receive written confirmation from a representative of our firm to put coverage in force or make changes to an existing policy.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 11 2022 - 15:23:22 CET

This message: [ Message body ]
Next message: Krishnaprasad Yadav: "Re: RE: Block connection from SQL developer"
Previous message: Jeff Smith: "RE: RE: Block connection from SQL developer"
Maybe in reply to: Scott Canaan: "SQL Server To Oracle"
Next in thread: Mladen Gogala: "Re: Block connection from SQL developer"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message