Re: SQL Server 2000 Migrate to Oracle

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41330969$0$2569$afc38c87_at_news.optusnet.com.au...
>C Stabri wrote:
>
>> I am trying to get some tables out of SQL Server 2000 and into Oracle
>> 9i. However I am encountering problems.
>>
>> 1. Many of the tables and column names in SQL server are > than 30
>> Characters which meansa any auto inport I do fails.
>> Is there a way to get around this.
>
> Yup. Open the relevant table in SQL Server, and start editing the column
> names.
>
> I'm sure you will tell me there's a perfectly good reason why your column
> names are so ridiculously long. But I won't believe it, so don't.

I disagree, well not with ridiculously long column or table names per se, but I do think that the 30 character limit on identifiers is overly restrictive. In particular I'd like to, but can't name constraints according to a convention like this <constraint_type>_<object_names>_<column_names>

eg a hypothetical foreign key that links a general ledger customer_id with a customer tables primary key of customer_id might be named

FK_GENERAL_LEDGER_CUSTOMER_ID_CUSTOMER_CUSTOMER_ID which is 50 characters. Now this is not a big deal, its isn't usually hard to come up with suitable abbreviations, but it is a restriction, and I don't think my desire to name constraints like this is irrational.

>
>> 2.The Create table statements for the SQL database are not
>> compatabible with ORacle Create table statements.
>
> Gosh. You mean SQL Server isn't compatible with Oracle?! Stop the presses.
> Er.... Hang on... Why does Windows complain about my perfectly legitimate
> "rmdir" command?

mine doesn't :)

C:\Documents and Settings\Niall>mkdir test

C:\Documents and Settings\Niall>dir
 Volume in drive C has no label.
 Volume Serial Number is E07F-1086

 Directory of C:\Documents and Settings\Niall

25/11/2004  13:09    <DIR>          .
25/11/2004  13:09    <DIR>          ..
22/11/2004  17:49               112 afiedt.buf
24/11/2004  23:07    <DIR>          Desktop
25/11/2004  11:19    <DIR>          Favorites
22/11/2004  20:26    <DIR>          My Documents
22/11/2004  18:56             2,871 sqlnet.log
10/10/2004  12:56    <DIR>          Start Menu
25/11/2004  13:09    <DIR>          test
10/10/2004  17:35    <DIR>          WINDOWS
               2 File(s)          2,983 bytes
               8 Dir(s)  64,327,254,016 bytes free

C:\Documents and Settings\Niall>rmdir test

C:\Documents and Settings\Niall>dir
 Volume in drive C has no label.
 Volume Serial Number is E07F-1086

 Directory of C:\Documents and Settings\Niall

25/11/2004  13:10    <DIR>          .
25/11/2004  13:10    <DIR>          ..
22/11/2004  17:49               112 afiedt.buf
24/11/2004  23:07    <DIR>          Desktop
25/11/2004  11:19    <DIR>          Favorites
22/11/2004  20:26    <DIR>          My Documents
22/11/2004  18:56             2,871 sqlnet.log
10/10/2004  12:56    <DIR>          Start Menu
10/10/2004  17:35    <DIR>          WINDOWS
               2 File(s)          2,983 bytes
               7 Dir(s)  64,327,237,632 bytes free

it doesn't like ls or grep though :)

> Wake up and smell the roses. They are two completely different products
> from
> two companies that would rather feed each other to some
> especially-ravenous
> crocodiles than co-operate on such matters as compatibility.

I agree, that said I do think it a sad reflection on the state of play that 2 industry leading players using ostensibly the same language, SQL, can't even get a simple CREATE statement consistent across databases.

> Learn each product for what it is, not for how it maps to some other
> product
> with which you happen to be marginally more familiar.
>
>> Bascially I need advices on getting 400 odd tables our of SQL server
>> into Oracle, any ideas where I have table and column anems > than 30
>> chars.
>
> You have a bunch of extraction technologies in SQL Server. You have SQL
> Loader and Export/Import in Oracle. Use them. Also pay attention to
> external table technology.

I'd add that your troubles are just starting, you've already discovered that you'll have to rewrite your SQL statements for the applications that access this database (otherwise ora-00942 table or view does not exists awaits you), you'll likely need to rethink the app as well, for example are you allowing the application to avoid locking by using artificially short transactions or by reading uncommitted data? You shouldn't do either of these in Oracle.

database platform porting is an entirely non-trivial task.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com