December 26, 2008 at 3:43 am
hi all
i want to migrate the database from sqlserver2005 to oracle 10g. I have migrated the tables,indexes,views,sequences,triggers through swisssql tool. now i want to migrate the stored procedures, we have 320 stored procedures. can any one tell me how to migrate that one.
thanks in advance.
aswin
December 26, 2008 at 4:57 am
Due to the variations between T-SQL and PL\SQL I would be really surprised if you were able to do a straight across migration. I would script out all the stored procedures and then start walking through the code to make all the changes necessary to get them to work properly in Oracle. Then test, test, test.
There are some good articles on the web for the differences / conversion from T-SQL to PL\SQL. Probably good to have a copy of one of these articles nearby when making the changes.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 26, 2008 at 5:00 am
stored procs and functions are a little harder to migrate; most of it is manually editing the procs, sorry.
things to look for:
10G uses the standard JOIN syntax, so that's a big help; queries featuring LEFT OUTER JOIN and such don't need to be translated to the old proprietary syntax of COLUMN = COLUMN(+)
Oracle doesn't like variable names that start with a @, In my case I scripted all the procs out, then did a find and replace of @ with "PRM_" so i could use that for parameter names.
code that is SQL server specific, like @@IDENTITY,Scope_Identity(),SET NOCOUNT ON, and things like that have to be trimmed out. Stuff featuring that needs to be rewritten to get the values you inserted, but SET commands cna just be whacked.
Cursors have a different syntax..search for cursor in your scripted out procs so you know how many are affected.
getdate() and current_timestamp are the equivalent of SYSDATE.
cast and convert: there is a suite of functions that are TO_NUMBER, TO_DATE and other similarly named functions;
ISNULL(column, default_value) has the mirrored function NVL (Null Value), so that's a simple find and replace.
concatenation syntax like 'abc' + 'def' is a bit different; use the pipe character twice:'abc' || 'def' , so that's a find and replace that needs to be done carefully to decide wheter its actual math/addition, or concatenation in your procs.
bugs:
well not so much bugs, but things you'll find that are really different:
Oracle Dates in a where statement, if you try so to use WHERE DATECOLUMN = '12/26/2008' will probably fail, as Oracle requires the datatypes to match, so there's no implicit comversion; you have to update your code to say DATECOLUMN = TO_DATE('MM/DD/YYYY','12/26/2008' )
CASE Statement syntax is limited:
SQL allows 2 styles of CASE statements:
CASE FIELDNAME WHEN VALUE THEN TRNASLATEDVALUE END
or
CASE WHEN FIELDNAME = VALUE THEN TRANSLATEDVALUE END
Oracle only uses the second style...so you need to search for CASE and change accordingly.
backup and restores suck; I'd say it takes a minimum of ten times longer to restore any database; In sql my 200Meg db takes less than a minute to restore; in Oracle it's about an hour. maybe I'm doing it wrong, but it sucks a s a developer to need to restore a database to retest, and have to wait for that.
That's all I can think of for now, but keep us posted on how your conversion goes; other's can learn from your experience.
Lowell
December 26, 2008 at 5:03 am
Nice Lowell! I haven't worked with Oracle for a few years so, I couldn't pull all those details out like that but glad you were able to! :w00t:
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 26, 2008 at 2:30 pm
I'd say that to get performant code one simply needs to understand what each proc does and rewrite it from scracth. Because especially if your SQL Server version was a bit old then they had simply many architectural differences (locking as probably biggest example) and some technical stuff which has been done in SQL Server is absolutely not needed in Oracle and probably might be situations also other way round.
Some comments about this:
Lowell (12/26/2008)
10G uses the standard JOIN syntax, so that's a big help; queries featuring LEFT OUTER JOIN and such don't need to be translated to the old proprietary syntax of COLUMN = COLUMN(+)
So called ANSI syntax is avaliable since 9.1 for a complete comparison of join types as such and across Oracle and SQL server one can look here in SQL join types.
Oracle doesn't like variable names that start with a @, In my case I scripted all the procs out, then did a find and replace of @ with "PRM_" so i could use that for parameter names.
Oracle do like identifiers (variable names as subset of them) starting with letter and followed by letter, numerals, dollar signs, underscores, and number signs.
Cursors have a different syntax..search for cursor in your scripted out procs so you know how many are affected.
Better actually think twice whether you really need cursor and are not able to rewrite cursor just as simple SQL statement. Also think about using bulk operations thus avoiding pl/sql and sql engines switch for each cursor loop again and again
getdate() and current_timestamp are the equivalent of SYSDATE.
Actually there is difference between sysdate and current_timestamp (which also can be used in Oracle), because date data type contains seconds, but timestamp data type contains also fractional seconds (up to 9 digits after the comma)
cast and convert: there is a suite of functions that are TO_NUMBER, TO_DATE and other similarly named functions;
Cast also works in Oracle.
ISNULL(column, default_value) has the mirrored function NVL (Null Value), so that's a simple find and replace.
Probably better use coalesce in both SQL Server and Oracle.
well not so much bugs, but things you'll find that are really different:
Oracle Dates in a where statement, if you try so to use WHERE DATECOLUMN = '12/26/2008' will probably fail, as Oracle requires the datatypes to match, so there's no implicit comversion; you have to update your code to say DATECOLUMN = TO_DATE('MM/DD/YYYY','12/26/2008' )
The problem actually is related to Oracle national language settings (which includes also date format mask), which can be different for each client (depending on regional settings for windows for example), so better always specify the format as suggested (just flip the actual value and format mask, because actual value is first argument and optionally format mask second).
CASE Statement syntax is limited:
SQL allows 2 styles of CASE statements:
CASE FIELDNAME WHEN VALUE THEN TRNASLATEDVALUE END
or
CASE WHEN FIELDNAME = VALUE THEN TRANSLATEDVALUE END
Oracle only uses the second style...so you need to search for CASE and change accordingly.
Actually one can use both of them.
backup and restores suck; I'd say it takes a minimum of ten times longer to restore any database; In sql my 200Meg db takes less than a minute to restore; in Oracle it's about an hour. maybe I'm doing it wrong, but it sucks a s a developer to need to restore a database to retest, and have to wait for that.
Look at tool called RMAN and start reading Oracle® Database Backup and Recovery Basics and after that Oracle® Database Backup and Recovery Advanced User's Guide 😉
Also look at various flashback features, which allows to get old data or just flashback database just without any restore at all (of course depending on change amount and time).
A few additional comments:
1) work with open mind starting with the premise that each DBMS is different and even the same terms may mean different things - a small comparison of them.
2) Read about packages. Understand them and you'll soon start really love them and hate standalone procedures 🙂
3) I think it is better to ask questions in the target environment forums, in your case Oracle - for example this forum might be good starting point.
Gints Plivna
http://www.gplivna.eu
December 30, 2008 at 8:24 pm
Hi All,
Thanks for the sugessitions.I did this through oracle sql devloper tool(client tool). every thing was migrated. but i have some compiling errors on procedures. have to work on it,
thanks for the help
aswin..
December 31, 2008 at 3:24 pm
More items to consider...
As a substitute for the CASE statement you might also want to look at the Oracle DECODE() function.
Date "math" is a little tricky. I haven't found any comparable functions for DATEADD and DATEDIFF.
An interesting function is TRUNC() which allows you to TRUNCate the time interval from SYSDATE. So, you can do limited date math
I believe the TO_DATE() function that was listed was incorrect, it should be TO_DATE(STRING_DATE, FORMAT_OF_STRING_DATE).
Assignment of values to variables is done without a set statement but instead used := for the assignment.
Packages are VERY useful and there is no SQL Server equivalent. Packages and Package Bodies work hand-in-hand. It helps me to think of the Package as the interface and the Package Body as the implementation of the interface. A Package [Body] may contain both procedures and functions {not sure about views} that are only declared and accessible by using the Package. A Package acts like a traditional class structure where you can centralize and organize business logic.
If I could put anything on the SQL Server development wish list Packages would be it.
Many people will tell you that PL/SQL is more powerful than than T-SQL. Well that's pretty much SQL stupidity. They are both powerful programming languages which are designed to support and work with their RDBMS. I'm very much a SQL Server guy however, there are things Oracle has that I think are pretty great (Packages) and things which I think are missing from Oracle (better date handling).
--Paul Hunter
December 31, 2008 at 5:49 pm
aswanidutt.dasara (12/26/2008)
hi alli want to migrate the database from sqlserver2005 to oracle 10g. I have migrated the tables,indexes,views,sequences,triggers through swisssql tool. now i want to migrate the stored procedures, we have 320 stored procedures. can any one tell me how to migrate that one.
thanks in advance.
aswin
Use the SQl server Migration Assistant. It's Free from Microsoft.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 31, 2008 at 7:13 pm
and the data type varchar2....
may be u'll also need NVL2 as well... as mentioned cast and convert needs to be replaced with specifics like to_string, to_Date, to_number etc...
There are other tools also available on internet that do the migration besides what suggest by Barry.
December 31, 2008 at 8:13 pm
Oops, sorry I'm going the wrong way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 9:26 am
Hi,
When you migrate your PL/SQL code from Oracle to SQL Server, it would be good re-design the objects. coz There are features suipported in Oracle are not available in SQL Server and vice versa.
EX: Oracle you can use ref curors but you dont have similar in SQL Server. SQL Server gives lot of flexibility interms of retuning the resultsets to application.
Like this there are several things are not common or the things which you are doing in PL/SQL can be better handled in T-SQL.
So you need to take time to go thru each and every object and convert. Before this you need to have complete grip on T-SQL and PL/SQL to get the best out of it.
Thanks -- Vj
February 10, 2009 at 12:43 pm
[font="Verdana"]Some good answers. I would only add a couple of caveats.
SQL Server (by default: yes, you can change it) is case insensitive, case preserving. That means if you name a table Abc in SQL Server, you can refer to it as ABC, Abc, abc, and they will all work.
Oracle (by default, you can change it as well) is semi-sort-of case sensitive, semi-sort-of non case preserving. The same example, if you create a table named Abc in Oracle, it will (by default) actually create it as ABC, and it will change any references to ABC to match.
The trick is that you can create case sensitive names in Oracle by putting double quotes around the name. For example, "Abc". If you do so, you will always have to refer to that table as "Abc", with the double quotes.
And of course, most tools that port from SQL Server to Oracle preserve the case, and hence all your queries now need to refer to tables with double quotes around the name.
The simple solution is just to convert all table (view, index, etc) names to upper case.
Another thing I might mention is that in SQL Server, statements like the following are quite valid:
select current_timestamp;
In Oracle, the equivalent would be:
select current_timestamp
from DUAL;
One final issue: any references to system variables (like @@servername) or system tables/views (like systables) won't work. You will need to recode for the Oracle equivalents.
Good luck!
[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply