have written a couple of SQL Routines for Derby and am recently looking into possibly re-using some of the code on another DBMS system also supporting Java (PostgreSQL pl/Java). pl/Java for me is more a playground and subject of curiosity then a real intention to use it productively at this stage. Need to say the promise of Java in the data base is - from a portability point of view - limited as my observations so far show implementations are back end specific in many cases. Maybe Apache Derby and Oracle would be more compatible, but Derby and PostgreSQL are not too often. However when there are multiple implementation approaches possible, one might be favoured over the other when portability is considered a requirement. Specific question: If there is a need for returning multiple out parameters from a routine (just one row, not a set of rows), one might choose to implement a procedure in Derby like this: CREATE PROCEDURE xy(IN CLIENTID integer, OUT LASTNAME varchar(30), OUT FIRSTNAME varchar(30) or CREATE PROCEDURE xy(IN CLIENTID integer) DYNAMIC RESULT SET 1
Is one of these approaches to be favoured over the other? What is the best pratice here? Any one to be favoured over the other when having Oracle portability in mind? Thanks a lot for sharing your experience and advise. Kind regards Thomas
