Oracle to SQL Server/MY SQL Migration [message #391503] |
Thu, 12 March 2009 07:01 |
|
Hi All,
First of all let me tell you i dont have much knowledge on data migration from one database to another.
There was some discussion going on about a project which involves Data Migration from Oracle to SQL Server, is there any tool which does that, I heard that there is a DTS in SQL Server which does that, i dont have any clues from Oracle Side..
Can anybody please suggest me, I would appreciate if you could provide me some details so that i can discover and dig in.
Regards,
Ashoka BL
Bengaluru
|
|
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #409804 is a reply to message #391503] |
Wed, 24 June 2009 02:44 |
|
@Frank,
I have some confusions, so thought of opening this post again.
"If I need to migrate all database objects from MYSQL to Oracle or viceversa, Do i need to write any code ?"
I have seen a tool "SwisSQL" which will data migration, what about Stored procedures(Procedures,Functions and Packages)
I have one task to do, which is migrating Oracle Stored Procedures to either MYSQL or SQL Server
Regards,
Ashoka BL
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #409819 is a reply to message #391503] |
Wed, 24 June 2009 03:15 |
|
@Michel,
Thanks for the reply,
What sort of Code do we need to write ? Please give me some example,
Assume that I have copied all the tables/views/indexes from Source(SQL Server) to Target(Oracle)
Do we have some built in Packages for data migration ?
|
|
|
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #409973 is a reply to message #409966] |
Wed, 24 June 2009 11:14 |
|
@gentlebabu,
What if It is a very lenghty stored procedure, Do we have any tools which will take care of checking the syntaxes and converting it to a desired format ?
Please share your experience as you have mentioned that you have done it before.
What is the normal tendancy of a Data Migration Project, does it involve converting stored procedures/triggers as well ?
|
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #410026 is a reply to message #391503] |
Wed, 24 June 2009 21:47 |
|
Hi,
Thanks for the information,
You need a developer who knows both databases to to the porting or even a complete re-write.
Assume If i need to convert an SQL Server procedure to Oracle Server Procedure, It means that I need to understand the SQL Server Procedure and rewrite it in Oracle using Oracle Stored procedure.
But will it going to be a tedious process, We need to know how the procedure has been built, how does it linked to a different procedure (or function).
IF anybody worked on a migration project, would you suggest me the steps involved in it,And is it a normal situation where in the Data migration Project involves migrating SPs as well.
Regards,
Ashoka BL
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #410086 is a reply to message #410031] |
Thu, 25 June 2009 02:36 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
And when you think about it, the steps ...
1) Read SQLServer procedures.
2) Figure out what they do.
3) Adjust the processing so that it fit's the Oracle way.
4) Write Oracle procedures.
*Might* actually take longer than to re-write the Oracle procedures from the original specifications, and never reading the SQLServer ones.
For example, what is done with three procedures, a CMD script and a few temporary tables COULD possibly be done in Oracle with one procedure and a cursor loop.
When you would have to declare dozens of customs types and dozens of variables in SQLServer, perhaps you could just use a ROWTYPE in Oracle.
One example that comes to mind, I once took about two days to understand what a 3000 line SQLServer procedure did, and then only 2-3 hours to rewrite it into about 200 lines of Oracle code.
|
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #410108 is a reply to message #410100] |
Thu, 25 June 2009 05:02 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Then try to migrate the following simple procedure with that tool, and see what happens:
CREATE OR REPLACE FUNCTION save_to_number(INCHAR IN VARCHAR) RETURN NUMBER AS
v_temp_number NUMBER;
BEGIN
v_temp_number := To_Number(INCHAR);
RETURN v_temp_number;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
/
The result is:
--ADVENTNET DROP SCRIPTS
If Exists ( SELECT name
FROM sysobjects
WHERE name = 'save_to_number'
AND type = 'FN')
DROP FUNCTION save_to_number
GO
CREATE FUNCTION save_to_number
(
@INCHAR VARCHAR(4000)
)
RETURNS FLOAT
AS
BEGIN
DECLARE @adv_rowcount INT
DECLARE @adv_error INT
DECLARE @v_temp_number NUMERIC(38)
SELECT @v_temp_number = CONVERT(NUMERIC(8, 2), @INCHAR)
RETURN @v_temp_number
GOTO ExitLabel1
Exception1:
BEGIN
RETURN -1
/* AdventNet SwisSQL (Oracle To SQL Server) : Manual Intervention to verify Exception is required */
END
ExitLabel1:
RETURN @v_temp_number
END
GO
########################################################################################################
--ADVENTNET DROP SCRIPTS
IF Exists ( SELECT name FROM sysobjects
WHERE name = 'POPULATE_SQLERRM' AND type = 'P')
DROP PROCEDURE POPULATE_SQLERRM
GO
CREATE PROCEDURE POPULATE_SQLERRM
AS
BEGIN
--ADVENTNET DROP SCRIPTS
IF Exists ( SELECT name FROM sysobjects
WHERE name = 'ADV_SQLERRS' AND type = 'U')
BEGIN DROP TABLE ADV_SQLERRS END
CREATE TABLE ADV_SQLERRS (NAME VARCHAR(100),ERROR_CODE INTEGER, ERROR_MESSAGE VARCHAR(4000))
INSERT INTO ADV_SQLERRS VALUES ('DUP_VAL_ON_INDEX',-1,'ORA-00001: unique constraint (.) violated' )
INSERT INTO ADV_SQLERRS VALUES ('INVALID_CURSOR',-1001,'ORA-01001: invalid cursor')
INSERT INTO ADV_SQLERRS VALUES ('INVALID_NUMBER',-1722,'ORA-01722: invalid number')
INSERT INTO ADV_SQLERRS VALUES ('ZERO_DIVIDE',-1476,'ORA-01476: divisor is equal to zero')
INSERT INTO ADV_SQLERRS VALUES ('TOO_MANY_ROWS',-1422,'ORA-01422: exact fetch returns more than requested number of rows')
INSERT INTO ADV_SQLERRS VALUES ('NO_DATA_FOUND',100,'ORA-01403: no data found')
INSERT INTO ADV_SQLERRS VALUES ('ACCESS_INTO_NULL',-6530,'ORA-06530: Reference to uninitialized composite')
INSERT INTO ADV_SQLERRS VALUES ('CURSOR_ALREADY_OPEN',-6511,'ORA-06511: PL/SQL: cursor already open')
INSERT INTO ADV_SQLERRS VALUES ('VALUE_ERROR',-6502,'ORA-06502: PL/SQL: numeric or value error')
END
GO
--ADVENTNET SCRIPTS to be executed
EXEC POPULATE_SQLERRM
GO
--ADVENTNET DROP SCRIPTS
IF Exists ( SELECT name FROM sysobjects
WHERE name = 'ADV_SET_EXCEPTION' AND type = 'P')
DROP PROCEDURE ADV_SET_EXCEPTION
GO
CREATE PROCEDURE ADV_SET_EXCEPTION ( @name VARCHAR(50) )
AS
BEGIN
DECLARE @context_info_var VARBINARY(128)
DECLARE @len INTEGER
SELECT @len = LEN(@name)
WHILE @len < 30
BEGIN
SELECT @name = @name + SPACE(1)
SELECT @len = @len + 1
END
SELECT @context_info_var = CAST (@name AS VARBINARY(128))
SET CONTEXT_INFO @context_info_var
END
GO
--ADVENTNET DROP SCRIPTS
IF Exists ( SELECT name FROM sysobjects
WHERE name = 'ADV_GET_SQLERRM' AND type = 'FN')
DROP FUNCTION ADV_GET_SQLERRM
GO
CREATE FUNCTION ADV_GET_SQLERRM (@name VARCHAR(50) )
RETURNS VARCHAR(4000)
AS
BEGIN
IF ISNUMERIC(@name) = 1
BEGIN
RETURN 'User-Defined Exception'
END
DECLARE @context_info_var VARBINARY(128)
DECLARE @adv_sqlErrm VARCHAR(4000)
SELECT @context_info_var = CONTEXT_INFO FROM master.dbo.sysprocesses WHERE SPID=@@SPID
SELECT @adv_sqlErrm = CAST (@context_info_var AS VARCHAR)
SELECT @adv_sqlErrm = SUBSTRING(@adv_sqlErrm,1,30)
SELECT @adv_sqlErrm = RTRIM(@adv_sqlErrm)
SELECT @adv_sqlErrm = error_message FROM adv_sqlerrs WHERE name = @adv_sqlErrm
RETURN @adv_sqlErrm
END
GO
--ADVENTNET DROP SCRIPTS
IF Exists ( SELECT name FROM sysobjects
WHERE name = 'ADV_GET_SQLCODE' AND type = 'FN')
DROP FUNCTION ADV_GET_SQLCODE
GO
CREATE FUNCTION ADV_GET_SQLCODE (@name VARCHAR(50) )
RETURNS INTEGER
AS
BEGIN
IF ISNUMERIC(@name) = 1
BEGIN
RETURN 1
END
DECLARE @context_info_var VARBINARY(128)
DECLARE @adv_sqlErrm VARCHAR(4000)
DECLARE @adv_sqlcode INTEGER
SELECT @context_info_var = CONTEXT_INFO FROM master.dbo.sysprocesses WHERE SPID=@@SPID
SELECT @adv_sqlErrm = CAST (@context_info_var AS VARCHAR)
SELECT @adv_sqlErrm = SUBSTRING(@adv_sqlErrm,1,30)
SELECT @adv_sqlErrm = RTRIM(@adv_sqlErrm)
SELECT @adv_sqlcode = error_code FROM adv_sqlerrs WHERE name = @adv_sqlErrm
RETURN @adv_sqlcode
END
GO
And the code doesn't even work, it throws a "Error Converting datatype varchar to numeric" error when there are non-numeric characters in the input string.
It is faster in 99% of all cases, and better in 100% of cases to rewrite the code on the target system than to try messing with automated conversion tools.
|
|
|
|
|
|
|
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #410200 is a reply to message #391503] |
Thu, 25 June 2009 11:36 |
|
Babu,
That's awesome, When I saw the video, I observed only the Database objects apart from Procedures. But Your example suggests that It wil be taken care,
May be we need to try out some complicated stuffs and see whether the tool is capable of handling it.
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #410599 is a reply to message #391503] |
Mon, 29 June 2009 03:56 |
Groutad
Messages: 4 Registered: June 2009
|
Junior Member |
|
|
To perform data migration, you could look at an open source ETL tool. It can perform the migration from an Oracle base to MySQL as it has adapted connectors for both.
Talend Open Studio (Talend.com) is an open source ETL tool for data integration and migration experts. The program also has a user-friendly GUI, easy to learn for a non-technical user.
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #410608 is a reply to message #410600] |
Mon, 29 June 2009 04:16 |
Groutad
Messages: 4 Registered: June 2009
|
Junior Member |
|
|
Hi Ashoka,
Actually, Talend Open Studio can migrate the data to a sql server. About stored procedures in Oracle, Talend has not implemented this feature.
Have you used Talend in previous data integration / migration jobs?
Thanks.
|
|
|
|
Re: Oracle to SQL Server/MY SQL Migration [message #419942 is a reply to message #391503] |
Wed, 26 August 2009 04:43 |
wonn1377
Messages: 3 Registered: August 2009 Location: ny
|
Junior Member |
|
|
I use data loader for migrating almost any data, it helps me to convert Oracle to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool, i downloaded on www.???.com
[Updated on: Wed, 14 October 2009 09:32] by Moderator Report message to a moderator
|
|
|