[Pljava-dev] Re: PL/SQL oracle java procedures compatibility

Aditya,

> I have an oracle 8i database that uses java stored procedures... We
> are planning on moving this database to postgreSQL, and one important
> thing is that the stored procedures need to work correctly. I came
> across this project, and i want to know if you have any idea on how
> easy/difficult this would be.
>
> How compatible is PL/JAVA with the SQLJ standard and how compatible
> the code is to oracle's implementation of java stored procedures
>
> I would greatly appreciate any help
> Thanks in advance
>
One of the objectives with PL/Java is to provide a way to migrate from
existing databases such as Oracle and DB2 where Java stored procedures
has been available for some time so your opinion about PL/Java is
important. If we can help you with your move to PostgreSQL in ways that
will improve PL/Java there is some interest to do so.

The PL/Java is fairly close to the SQLJ standard in some areas. In
particular:
- The sqlj functions to install and maintain jar files are implemented
according to the spec. complete with SQL deployment descriptors etc.
- A standard JDBC driver is used internally with the standardised name
"jdbc.default.connection" to get access to the current connection.
- Parameter and return type mappings are according to standard.

Some things are not standard though. PostgreSQL supports only functions
and triggers, not support stored procedures (i.e. there's no "CREATE
PROCEDURE" command). Please note that this is not a PL/Java limitation.
Our intention is to equip PL/Java with stored procedure support as soon
as PostgreSQL itself includes it.

UDT (User Defined Types) are supported but at present there's no way of
writing UDTs using Java as the implementation language. This is mainly
due to a syntax restriction in PostgreSQL. The SQL standard stipulates
that UDT declarations can contain a "language" clause. PostgerSQL
doesn't implement that at this time. Again, our intention is to provide
this once PostgreSQL becomes standards compliant.

The SQL standard says very little (or nothing at all) about language
mapping of triggers. The PL/Java approach is to resolve row access in
triggers using the standard JDBC interface java.sql.ResultSet. The
standard stipulates that this is what should be used for complex types.
If I remeber correctly however, Oracle has an other (non standard) way
of doing this. In addition to the JDBC interfaces, one interface was
added to enable access to trigger specific data.

In order to help you estimate the amount of work involved moving from
Oracle to PostgreSQL, I must of course know more about your database and
the kind of Java functionality you implement.

Kind regards,

Thomas Hallgren