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

[Thomas Hallgren]

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