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

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Tue, 23 Jan 2007 14:10:31 -0600
Message-ID: <7b8774110701231210q7b276205yb6e22e03615c3df7@mail.gmail.com>

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_at_gmail.com> 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

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

Received on Tue Jan 23 2007 - 14:10:31 CST