April 15, 2009 at 4:11 pm
Hi all,
I'm new to sql server, but developed an application with a db on Oracle 9i.
An optional client want the application, but on SQL Server 2005, so:
I've installed SQL Server 2005 Standard edition, Created a database, and then, migrated the Oracle database to my SQL Server Database, by using the SSMA for Oracle.
Most of the process went good, except of some problems with ref_cursors, and table locking - not something that I can't live with.
My major problems are:
1. Is there a way to modify the converted procedures?
Each time I try to change a stored procedure, and then execute the changes to the database, I get errors like:
msg 102, level 15, state 1, line # (# - line no)
Incorrect syntax near 'my procedure name' (which converted with $IMPL at the end of its name,
and then
msg 137, level 15, state 1, line # (# - line no)
Must declare the scalar '@some Variable'...
2. Is there way to keep develop my app in Oracle DB, and convert the changes each time I want?
Thanks in advance
Ohad
April 15, 2009 at 5:13 pm
It will be easier to move the application to 10g RC2 and then to 2005 because both are easy to integrate with limited issues, while 9i will require some rewrites.
Kind regards,
Gift Peddie
April 16, 2009 at 9:12 am
Hi,
Thanks for reply.
My oracle version is 9.2.0.1 and to upgrade to 10g I need to be in 9.2.0.4, which means that I have to install a patch and then upgrade.
This is a bit problem.
Is there any other way to rewrite the converted code?
April 16, 2009 at 9:31 am
Here is a free book from Microsoft that will help you to SQL Server 2000 because that is the version in use with 9i.
http://technet.microsoft.com/en-us/library/bb496494.aspx
Kind regards,
Gift Peddie
April 20, 2009 at 12:50 pm
Gift Peddie (4/15/2009)
It will be easier to move the application to 10g RC2 and then to 2005 because both are easy to integrate with limited issues, while 9i will require some rewrites.
:w00t: If I get it right original poster issue is about how to move Oracle backend code e.g. storedprocs/functions/packages to SQL Server e.g. storedprocs.
Could you please elaborate about how Ora10g PL/SQL is more easy to integrate -or move to SQL Server 2005 than Ora9i PL/SQL code?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2009 at 1:03 pm
Could you please elaborate about how Ora10g PL/SQL is more easy to integrate -or move to SQL Server 2005 than Ora9i PL/SQL code?
That depends on the platform in .NET 10g RC2 comes with built in Data Access features that enables Transations and location based Blob method we had to upgrade to 10g RC2, the project started with 9i but had to move to 10g RC2 and the SQL Server was 2005. You must know .NET seldom use Oracle packages or functions including the associative arrays.
Kind regards,
Gift Peddie
April 20, 2009 at 4:09 pm
Gift Peddie (4/20/2009)
Could you please elaborate about how Ora10g PL/SQL is more easy to integrate -or move to SQL Server 2005 than Ora9i PL/SQL code?
That depends on the platform in .NET 10g RC2 comes with built in Data Access features that enables Transations and location based Blob method we had to upgrade to 10g RC2, the project started with 9i but had to move to 10g RC2 and the SQL Server was 2005. You must know .NET seldom use Oracle packages or functions including the associative arrays.
I see, good answer but to the wrong question.
Poster is asking how to do code maintenance to both an Oracle and a SQL Server versions of the same code. Poster is looking for a way of doing code maintenance on Oracle side and get those changes moved somehow to SQL Server side -which is not gonna happen in the foreseable future. PL/SQL != Transact-SQL
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2009 at 4:18 pm
I'm new to sql server, but developed an application with a db on Oracle 9i.
An optional client want the application, but on SQL Server 2005, so:
I've installed SQL Server 2005 Standard edition, Created
Actually I understand the need differently more like my project for 18 states some run in SQL Server 2005 and others in Oracle. We found changing 10g RC2 to 2005 was easy and by the way we also used Oracle SQL Developer. So the first step is to move objects then change the DML. And I posted a complete book for Oracle 9i to 2000 manually porting by Mircosoft.
Kind regards,
Gift Peddie
April 20, 2009 at 4:23 pm
I think you may have overlooked the part where poster says: Is there a way to modify the converted procedures?
In my book that means poster has two versions of the backend code, one coded on PL/SQL for Oracle and one coded in Transact-SQL for SQL Server.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2009 at 4:30 pm
Most of the process went good, except of some problems with ref_cursors, and table locking - not something that I can't live with.
Yes but I was looking more at the above it is better if you are using 10g RC2 so you could use ODP.NET to use Ref_cursor for both insert and output, because with the Microsoft provider it is read only. And I have covered the transaction. So the user needs to do the manual transfer.
In my book that means poster has two versions of the backend code, one coded on PL/SQL for Oracle and one coded in Transact-SQL for SQL Server.
No my understanding the user needs to create a SQL Server 2005 copy from Oracle 9i database and that is what I was helping assuming a .NET application.
Kind regards,
Gift Peddie
April 20, 2009 at 4:43 pm
Okay -this is gonna be my last posting on this matter.
Let me copy/paste poster's questions...
1. Is there a way to modify the converted procedures?
2. Is there way to keep develop my app in Oracle DB, and convert the changes each time I want?
... isn't it clear?
Poster is talking about the backend not the frontend.
Key words are: modify, converted, procedures.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2009 at 4:49 pm
I am also talking about backend but with the middle issues included not actually front end. Chapter 11 in the link I posted will provide most of the conversion needed.
http://technet.microsoft.com/en-us/library/bb496494.aspx
Kind regards,
Gift Peddie
April 21, 2009 at 6:39 am
Oh boy; what you say holds true but you have never even attempted to answer the poster's question. 😀 You just keep randombly answering questions you make up in your mind.
Hard to keep alive a conversation about last football season when you keep talking about Michael Jordan, A-Rod and how good of a team the NY Rangers are. 😛
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 21, 2009 at 7:02 am
Paul,
This thread is not yours I have provided what the OP will use to convert Oracle to SQL Server all you have done is critize what I have done without providing one technical conversion code or instruction. So you are the one full of your self so please be civil and don't talk to me again. The user have developed an application in Oracle what I have provided can be used to convert at least 75 percent of the code. Microsoft also calls what I provided data access so it may not be right in your Oracle context but the person is looking to create SQL Server copy so Oracle context is actually not relevant because the person already knows Oracle.
The user is having problem with Ref_Cursor I got this from the book I posted.
Oracle
DECLARE
CURSOR SHIPPER_CURSOR IS SELECT * FROM SHIPPERS;
SHIPPER_REC SHIPPERS%ROWTYPE;
BEGIN
OPEN SHIPPER_CURSOR;
LOOP
FETCH SHIPPER_CURSOR INTO SHIPPER_REC;
EXIT WHEN SHIPPER_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('SHIPPER NAME IS '||SHIPPER_REC.COMPANYNAME);
DBMS_OUTPUT.PUT_LINE('SHIPPER PHONE IS '||SHIPPER_REC.PHONE);
END LOOP;
CLOSE SHIPPER_CURSOR;
END;
SQL Server
DECLARE @SHIPPER_NAME VARCHAR(50),@PHONE VARCHAR(50)
DECLARE SHIPPER_CURSOR CURSOR FOR SELECT COMPANYNAME,PHONE FROM SHIPPERS;
BEGIN
OPEN SHIPPER_CURSOR;
FETCH NEXT FROM SHIPPER_CURSOR INTO @SHIPPER_NAME, @PHONE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'SHIPPER NAME IS ' + @SHIPPERNAME
PRINT 'SHIPPER PHONE IS ' + @PHONE
FETCH NEXT FROM SHIPPER_CURSOR INTO @SHIPPER_NAME, @PHONE
END
CLOSE SHIPPER_CURSOR
DEALLOCATE SHIPPER_CURSOR
END
The user is also having problems with Transactions.
Step 2: Transaction Management
After identifying all instances of SQL, transactions need to be carefully modified as part of the data migration process. This section discusses how to handle the transactions in SQL Server.
To begin, identify all of the transaction commands currently used with Oracle and implement them for SQL Server. In Oracle, all commands inside two transaction control language statements are considered as a batch. If a set of statements needs to be considered as a batch, then all the statements must be inside BEGIN TRANSACTION and COMMIT TRANSACTION commands. A discussion of these architecture topics is provided in Appendix A: SQL Server for Oracle Professionals.
Kind regards,
Gift Peddie
April 21, 2009 at 7:31 am
Well, that's the problem - poster never asked about instructions or tools to make a conversion. Poster knows how to do it.
Poster clearly asked if it was possible to do backend code maintenance on Oracle platform and somehow move it to SQL Server. By the way, I did answer poster's question.
Last but not least, don't take it personal; we all volunteer our time here just to help people and have some fun, isn't it? 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply