March 17, 2008 at 7:39 pm
I've recently started a new job and my role (as a DBA) is to assist with converting an Oracle 10G database to SQL2005. The tables were easy, but the stored procs and triggers are a totally different story. I installed an instance of 10g alongside my sql 2005 instance so I could use the SQL Server Migration Assistant for Oracle (SSMA). I'm down to 16 stored procedures, some of which are 4000 lines. All of these stored procs are underlying procs for reports, but noone can seem to give me any technical docs and/or reporting requirements for the procedures, therefore I'm stuck simply staring at the code and trying to make the best decisions/assumptions I can about what the procs are to accomplish. My biggest challenge has been taking the concatenated sql strings out of the procs and getting them into raw sql statements. I have no idea how the procedures got in this shape in the first place, but I'm stuck cleaning up the mess. I have almost NO Oracle experience, but I'm getting it now!! Other than the SSMA and the SwissSQL conversion tool, I haven't found any good tools to assist with this process (ie nightmare). These tools work well, but they can only do so much.
I'm also shocked that the company I'm working for doesn't have any technical docs about the reports. The sad thing is that they own the source code and have implemented this system for many clients, but apparantly this set of reports has never been converted from Oracle.
I've voiced my concerns to my tech. lead and have told him that what I've been tasked to do could take over a year if I'm the only one that is going to be working on it.
Would any of you happen to have any suggestions or advice on how to simplify this process? Any tools, web sites, whitepapers, etc. that might help w/some of the differences between Oracle and SQL Server would be greatly appreciated.
Thanks in advance
Dave
March 26, 2008 at 4:54 pm
I can only estblish a fact that you are suffering from high factor of data waste 😉
Gints Plivna
Gints Plivna
http://www.gplivna.eu
December 9, 2008 at 7:06 am
I'd the opportunity of doing it both ways e.g. from Oracle to SQL Server and from SQL Server to Oracle.
When it comes down to code here is how it works. You read the source platform code, understand what it does and write new code for the target platform. Believe it or not that's the easiest and faster way to get it done.
Good luck.
_____________________________________
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.December 10, 2008 at 6:15 am
I've converted from Oracle to MS SQL. When it comes to the sprocs and triggers; it's just hard work. There is no easy way around it. Like Paul said, you have to read the old and understand it and then rewrite it. For instance, Oralce uses many cursors, but in SQL that isn't necessary. You could rewrite and keep the cursors, but that wouldn't be very efficient. Eliminating the Oracle packages can also be a task.
It also comes down to TESTING. You definitely need a tester to verify that the reports are returning what is expected after, of course, you doing side by side testing of the queries.
There is no easy way to complete your conversion. Good Luck.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply