In Dynamics of Software Development Jim McCarthy makes the argument in #23 Portability is for Canoes. Well the product I am working on needs ro become a canoe. Currently it has a SQL Server backend and it has been sold into an Oracle only shop.
I expect to have a large number of posts on the pain of porting.
The product has been written using a large number (900 ish at the last count) of stored procedures. We have a handfull of views.
The fun part comes comparing Oracle to SQL Server:
In SQL Server parameters need to start with @.
In Oracle parameters may not start with @.
Oracle is case sensative by default.
SQL Server is case insensitive.
Not much of a problem as all of our tables are NAMED_IN_UPPER_CASE.
Oracle has a 30 character limit on database object names.
You know, stored procedures, tables, indexes, constraints.
We have 130 of these to rename.
The one that will bite us are the stored procedures that return reordsets. Oracle does not allow that. This is the fun one.
1) Oracle can return recordsets from a package via the pipelined clause. Check out Toms site for more info on how to do this
go to http://asktom.oracle.com and search for pipelined
btw- That site is a great resource, if your question has not already been asked Tom is usually pretty good about answering new ones.
2) If you really want you can have oracle store you’re objects in a case-sensitive format; just enclose the object name in double-quotes.
create table “MyTable” as select 1 col from dual;
select * from user_objects where object_name = ‘MyTable’;
If you look at my 2nd December post I have a neater solution to this already.