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.

This is how you can call functions in a sql call, such as:
SELECT SYSDATE + 99 FROM DUAL

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

Oracle is clearly insane.

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.

Benefit:

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.