[Haskell-cafe] re: Oracle stored procedures

[Leonel Fonseca]

Hi, Peter

I did and uploaded some examples regarding Oracle and Takusen and the
intended approach you would employ.

Example 01: Create table and some tuples.
http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29883#a29883

Example 02: Create a package with procedures to mantain the table.
http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29884#a29884

Example 03: Optional. See how it works in Oracle sqlplus.
http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29885#a29885

Example 04: The Takusen-Haskell program. It follows the scheme of example #03.
http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29894#a29894

Some comments on the topic:

This approach has limitations. Takusen (if not use in conjunction with
Template Haskell) will be bound up to iteratees of eight values. I
(and I'm pretty sure some other people) have successfully used
Template Haskell to overcome this limitation.
(I've been working in removing some boilerplate).

The all stored procedure approach is not a limitation as you can see
from example #04, unless these procedures return values these types:
Record Of <something>, complex objects (PL/SQL tables, objects, etc),
Boolean.

Oracle has several programatic interfaces, some that I remember: JDBC,
ODBC, OCI, OCCI and those for Windows .NET.

Takusen is a wrapper for OCI (Oracle Call Interface) which seems to me
pretty low level. Yet, I don't know if you can specify just the name
of the procedure.

Please note that if there is a procedure named close_accounts, by
writing "begin close_accounts; end;" you are not wrapping it in a SQL
statement. It actually is an anonymous PL/SQL block and it is needed
in that form to be processed by the server.

Oracle offers for Windows .NET: ODT (Oracle Developer Tools for Visual
Studio), ODP (Oracle Data Provider) and ODE (Oracle Database
Extensions). I did test (and taste) a bit of .NET tools with F#. It
was nice. Maybe you wish to try hs-dotnet with these.

A final remark, I have used Takusen both on Windows and Linux. The
given example was developed and tested actually on Windows.


Regards,

Leonel Fonseca.