Re: Helping developers write a pl/sql wrapper to translate a nested table to jdbc VARRAY

Charles, according to
http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14355/apxref.htm#i1004133 while you have a lot of restrictions for PL SQL data types, SQL user defined data types are supported by JDBC. So (pl sql array of records is 2 dimensional array in pl sql, which in my mind is closest to the database type - collection of objects) , my suggestion would be to use a SQL object type which correspond in attribute set to your record type of nb_job_labor.labor_tab and an SQL varray of this type, which correspond to the PL SQL Table . So, the wrapper procedure would accept an varray of objects, transform it into plsql table of records, call p_create, transform the plsql table of records back into varray and put it back into IN OUT parameter. It would not be possible with cast, but within nested for loops ( record by record, field by field ) and so probably not the fastest in the world, but it could do this transformation. If you could check with your java developers, that my assumption is correct (i.e. , jdbc can address the VARRAY OF OBJECTS ) - i could write a simple democase for pl sql part.

The ref cursor approach probably would not work, as you can only read result set from cursor, not write , IN OUT mode for ref cursor will simply mean, that you can assign another cursor to your ref cursor, but not another values to the result set.

Best regards

Maxim

Charles Schultz schrieb:
> 1. What should the wrapper do?
> Interface between the ERP procedure (p_create) and the java code
>
> 2. (combined with 3) Why should it be a IN OUT parameter?
> That is the way the ERP procedure is defined. If we can overload the
> wrapper so that one functions as input and another as output, that is
> fine, but nothing I have ever done before.
>
> Here is a snippet of the procedure I have to work with:
> PROCEDURE p_create
> (p_job_labor_tab IN OUT nb_job_labor.job_labor_tab,
> p_change_reason varchar2 default null,
> p_default_labor_ind varchar2 default 'N',
> p_warnings_out OUT gb_common_strings.err_type
> )
>
> nb_job_labor.job_labor_tab is a pl/sql table - the pl/sql record it is
> built on has 34 columns based on (anchored to) an existing database
> table. gb_common_strings.err_type is simply a varchar2, so no worries
> there.
>
> The procedure throws an error if the incoming table is empty (an
> application error, not an Oracle error).
>
> Bottom line: The procedure is given. I am working with the java
> developers to interface with the procedure, and they are open to
> suggestions. I was hoping that REF CURSORS might be the way to go, but
> that is not set in stone. Basically, we want something that is
> foremost quick and accurate, and if it can be elegant and simple at
> the same time, that is a bonus. =)
>

--
http://www.freelists.org/webpage/oracle-l