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.

It includes a few shortcuts to make life easier:

@FILENAME

This will excecute FILENAME (or FILENAME.SQL)

SHOW ERRORS

This will show the detailed errors for the last command.

L

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.

The main trick is to add:

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

//<![CDATA[
<!–
if (document.getElementById) {
document.write('’);
document.write(‘ .lnav { position: absolute; }’);
document.write(‘ .lnavch { margin-left:23.0ex;}’);
document.write(”);
}
//–>
//]]>

//<![CDATA[

//]]>

//<![CDATA[
<!–
// ———————————-
// 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
//]]>

//<![CDATA[

//]]>

//<![CDATA[

//]]>

//<![CDATA[

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

The cursor itself must be defined as a ref cursor :

CREATE OR REPLACE
PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR
END;

Oracle 9i introduced the SYS_REFCURSOR type for this purpose.

Here is a valid example:

CREATE OR REPLACE
PROCEDURE USP_GET_UNIT(p_recordset OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_precordset FOR
       SELECT UNIT, DESCRIPTION FROM UNIT;
END;

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