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

I am still pursuing this. Thanks for generous help from Job Miller, I have come to understand that a pl/sql table is different than a sql table is different than a database table. Yuck. Anyway, since java comes in via JDBC as SQL, we cannot interface directly with pl/sql tables and must get them to a supported sql type (ie, a sql table). I have come to understand that this can be implemented using REF CURSORS.

So now, how does one define a pipelined function to use a IN OUT REF CURSOR? I see Tom Kyte's pipeline
function<http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:246014735810>, but that only pipes an outgoing set. Google directed to me to a few sites that mirror the online documentation. For example: http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10800/dcitblfns.htm

Good stuff, but I am having trouble understanding it (remember, my pl/sql is still in the early stage). I guess I should first ask, is it even possible to use a REF CURSOR in this fashion? The aforementioned documentation seems to indicate that you must rely on a "weak" or sys_refcursor for some of these operations.

As to details of what I am trying to do, I am constrained by pl/sql types (a record anchored to a database table, and a table type of those records by index), and a 3rd party procedure that utilizes those pl/sql tables as IN OUT parameters. My goal is to create a wrapper that will accept a jdbc-compliant datatype (ie, REF CURSOR). I can create new sql types to facilitate this, but I cannot get rid of or negate the pl/sql table.

On 1/19/07, Maxim Demenko <mdemenko_at_gmail.com> wrote:
>
> Charles Schultz schrieb:
> > As pl/sql is one of my weak points, I was wondering if anyone had any
> > working examples of converting TABLE to VARRAY in a pl/sql wrapper? I
> > am browsing the documentation:
> >
> http://download-east.oracle.com/docs/cd/B19306_01/java.102/b14355/apxref.htm#BABFECBJ
> >
> > It gives a very limited example using BOOLEAN, which is probably the
> > easiest thing in the world to convert.
> > Any and all help would be much appreciate.
> > TIA,
> >
> > --
> > Charles Schultz
> Could you not just use cast ?
>
> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions016.htm#SQLRF00613
>
> Best regards
>
> Maxim
>

-- 
Charles Schultz

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