Executing Oracle stored procedures from Paradox isn't as straightforward as MS SQL or Interbase, but as I've discovered, it is just a matter of configuration and syntax - no tricks or hacks required.

Preface

This article documents the configuration and coding conventions that allow Oracle stored procedures to be called from Paradox.

Introduction

When the question of Oracle stored procedures has come up in the past, I personally have stated that you can't execute them directly from Paradox or ObjectPAL.

I was wr....  I was wron.... 

I was wrong.

In this article, I'll set the record straight, hopefully.  We'll start with the required configuration settings and coding practices.  Then we'll handle three separate cases:

1. Execution with no result set and no output parameters
2. Execution with a result set
3. Execution with output parameters

I'll keep this short and simple:  Below are the configurations that I've personally tested.  Your Mileage May Vary.

Use the MS ODBC for Oracle driver and create a System DSN to your database, then point a BDE alias at that DSN.  You can get away with using the BDE SQL Links driver for Oracle for the first scenario (no result set and no output parameters), but I recommend the MS ODBC for Oracle driver.  The Oracle ODBC driver doesn't appear to work very well with Paradox at all.

Make sure ROWSET SIZE=0.  Also consider setting SQLPASSTHRU MODE to SHARED NOAUTOCOMMIT or NOT SHARED.  Using AUTOCOMMIT won't affect your ability to execute stored procedures, but it will (in my experience) make transaction control impossible.

Scenario 1: No Result Set/No Output Parameters

It turns out that this scenario is every bit as simple as it would be with MSSQL or Interbase, it just takes a slightly different syntax - you have to execute it as part as an anonymous PL/SQL block.  In simple terms, you just need a BEGIN in front and an END; at the end.

Let's say you had a stored procedure that takes an order number and archives that order from the system: archive_order( orderno IN orders.orderid%type)

You'd execute it in Paradox as:

or from ObjectPAL as:

endmethod

Note: The techniques that follow are only available in Oracle 9.2.x and above.

Now things get a little trickier.  The reason is that Oracle doesn't return result sets from stored procedures the same way that MSSQL or Interbase do.  Oracle returns Reference Cursors instead.  The trick is to access the reference cursor from Paradox. 

One way to do this that appears to work from Paradox is through the use of Pipelined table functions.  This involves essentially jacketing the stored procedure call (the one that returns the result set) in a function that pipelines rows, then calling that function in Paradox through the TABLE() function.

What you wind up with is a SELECTable function.  So instead of:

spSQL = SQL
        begin 
           retrieve_order(526);
        end;
        ENDSQL

and finding a result set in ANSWER.DB, you'd just do a

spSQL = SQL
           SELECT * FROM TABLE(retorder_wrapper(526))
        ENDSQL

Scenario 3: With Output Parameters

Output parameters represent a problem similar to the one with result sets: Paradox has no direct way of obtaining their values.  The workaround here is to again use pipelined table functions to pipe the output parameters to a function which is recasted as a table and executed in a SELECT statement.

The obvious problem with this is that it is extremely challenging to execute a stored procedure that returns a result set *and* output parameters.

An Alternate Alternative

A simple workaround to the limitations of Scenarios 2 and 3 is to simply wrap the stored procedure in a procedure that writes out the result set and output parameters, as needed, to temporary tables and read from those in subsequent SELECTs from Paradox.  You can also use this approach with the pipelined table function approach where a stored procedure returns output parameters and a result set. 

Conclusion

Oracle stored procedures from Paradox is possible through a variety of alternatives, though some are a little ponderous.