Re: Helping developers write a pl/sql wrapper to translate a nested table to jdbc VARRAY
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Jan 2007 14:10:31 -0600
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. =)
On 1/23/07, Maxim Demenko <mdemenko@xxxxxxxxx> wrote:
Charles, could you elaborate a little bit, what should the wrapper do?
Is my understanding correct, that it should accept a weak ref cursor as
input parameter and return result set in form of a collection ( nested
table, varray or pl sql table)? - or , in opposite , take a collection
as input and return a ref_cursor which fetches the collection elements?
And what type should ( can ) be collection elements - scalars, or
collections or records as well ?
And why it should be a IN OUT parameter ( or it shouldn't) ?
--
Charles Schultz
Other related posts:
