Oracle vs SQL Server

I have been using SQL Server for the last few years.
It is very user friendly and has a gentle learning curve.

Investigating a port to Oracle has been a real culture shock.
Out of the box Oracle only has a command-line interface.
To get the equivalent of Query Analyser in Oracle you need to
use a third party tool such as TOAD or PL/Developer.

SQL Server is also more flexible when dealing with stored procedures – you can directly return recordsets. Oracle is very strict when dealing with stored procedures all output must be via parameters.

To Oracle’s benefit they have much stronger error handling. SQL Server stored procedures cannot be used to catch constraint validation errors. This is rather annoying and forces a look before you leap approach.

Useful SQLPlus Commands

The SQLPLUS utility is the Oracle equivalent of iSQL.

It includes a few shortcuts to make life easier:


This will excecute FILENAME (or FILENAME.SQL)


This will show the detailed errors for the last command.


This lists the last command entered.

Beware that stored procedures or functions cannot include blank lines (as far as i can tell – I got so many errors that I removed them).

Without some of these commands it is as user-friendly as a cornered rat.

Recordsets from Oracle Stored procedures

This is link to an article that explains how to Oracle stored procedures to return recordsets.

The main trick is to add:

document.write(‘ .scripthide { display: none; }’);
document.write(‘ .scriptinline { display: inline; }’);
document.write(‘ .scriptblock { display: block; }’);
<style type=”text/css”><!– .noscripthide { display:none; } .noscriptinline { display:inline; } .noscriptblock { display:block; } //–></style>
if (document.getElementById) {
document.write(‘ .script12hide { display: none; }’);
document.write(‘ .script12inline { display: inline; }’);
document.write(‘ .script12block { display: block; }’);

if (document.getElementById) {
document.write(‘ .lnav { position: absolute; }’);
document.write(‘ .lnavch { margin-left:23.0ex;}’);



// ———————————-
// visibility functions
function tog() {
// tog: toggle the visibility of html elements (arguments[1..]) from none to
// arguments[0]. Return what should be returned in a javascript onevent().
display = arguments[0];
for( var i=1; i






Extended Properties=”PLSQLRSet=1″ to your connection string.

The cursor itself must be defined as a ref cursor :

  TYPE cursor_type IS REF CURSOR

Oracle 9i introduced the SYS_REFCURSOR type for this purpose.

Here is a valid example:

    OPEN p_precordset FOR

On the delphi side don’t add the cursor as a parameter.

Oracle is Insane

Oracle has a built in table that always has one row.
The table is called DUAL.

This is how you can call functions in a sql call, such as:

SQL Server lets you call functions explicitly such as:
SELECT GetDate()

Oracle is clearly insane.

Oracle – Where is the up side?

I am in the process of porting a large SQL Server project to Oracle.
The product needs to support both products going forward.

The more I look at Oracle the less impressive it seems. Hopefully I will find some pleasant features.
The problem is that I need to stay portable.

The big benefit is the lack of lock-in to the windows platform.
This does mean that we can’t guarentee to always have the same version that the customer is using.

Downsides include:

SQL/PLus – this is the best tool that you get out of the box. It may be guarenteed platform portable but it belongs in the 1980’s. You really need TOAD or PL/SQL Developer.

SQL/PLus error handling is painful at best. I am trying to create a 200 table database from a translated script. Some of the tables are not there and it is painful to find what is wrong with the script.

Lack of domains. You can’t specifiy aliases for primative types such as STD_REFERENCE VARCHAR(20)

You can’t return recordsets from stored procedures.

It is a resource hog. It has seriously slowed down my addmitedly old development machine to a crawl.


Lack of the poorly implemented COLLATION issue in SQL Server.

The majoirity of my script conversion is being done using sed.
Hopefully I should be able to perform 90% of the store dprocedure translations with this tool.

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.