My Product Must Be A Canoe

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.

2 thoughts on “My Product Must Be A Canoe

  1. 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 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’;

Leave a Reply to chriseyre2000 Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s