Re: MS SQL 7 Script to Oracle

In an earlier post, Danial Morgan already pointed out that statements like "create database" simply don't make sense in Oracle. The same is true of many other things (including syslogin, sysobjects, sp_addlogin, dbname.dbo.objectname, etc). When I said almost, it's almost, but not all. You still have to work a little.

It's amazing how inefficient the original MS SQL script is: the goal is to create hoteluser with a null password, using default db master and @@language. All you need to do in Oracle is to use create user hoteluser ...

Of course you need to make sure the script doesn't break on errors, therefore all those "if exists" checking in the MS SQL script. If you don't want to do a lot of work in Oracle, all of them can be replace by a single exception clause. MS SQL uses these many if exists because it doesn't have a way to trap exceptions very gracefully.

If you are really looking for a short cut, I'd recommend that you polish up the MS SQL script, make it a procedure and see if you can migrate it to an Oracle procedure using Oracle Migration Workbench, downloadable from technet.oracle.com. But be warned: almost 100% of the converted scripts/procs need re-work.

Good luck
Jining Han
Sallie Mae

/****** Object: Login hoteluser Script Date: 1/27/2001 3:39:47 PM ******/
if not exists (select * from master.dbo.syslogins where loginname = N'hoteluser')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english' if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)   select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
  select @loginlang = @@language
exec sp_addlogin N'hoteluser', null, @logindb, @loginlang END
GO

In article <3A86AADC.8E984001_at_rsippel.de>,   Roland Sippel <hallo_at_rsippel.de> wrote:
> Jining Han wrote:
>
> In your scripts are very fundamental table and user creation processes
> and permission granting.
> Do a search/replace for all [], replace the
> go with /,
>
> ## ok its easy !
>
> get rid of all your variable declarations
>
> ## what means this delete all ?
> declare @logindb nvarchar(132), @loginlang nvarchar(132)
>
> then replace @
> with & and your are almost done.
>
> sure thats it ?
> N'guest'
> N'autoclose', N'false'
>
> in oracle i think
>
> N.'guest'
> N.'autoclose', N.'false'
>
> and IMHO master.dbo.syslanguages doesn´t exist
>
> if &loginlang is null or (not exists (select * from
 master.dbo.syslanguages where name
> = &loginlang) and &loginlang <> N.'us_english')
>
> Thanks Roland

Sent via Deja.com
http://www.deja.com/ Received on Mon Feb 12 2001 - 07:47:07 CST