Re: Helping developers write a pl/sql wrapper to translate a nested table to jdbc VARRAY
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx, mdemenko@xxxxxxxxxx
- Date: Tue, 23 Jan 2007 21:38:25 -0800 (PST)
charles,
as a summary of what I mentioned off-line, the steps are:
1. call wrapper and pass nested table type to wrapper (Nested Table is JDBC
compliant data type) so can be passed from java client.
2. wrapper builds pl/sql table of records from nested table type (to
interface with your PL/SQL ERP proc) by iteration of the nested table and
population of pl/sql table.. no CAST since CAST requires SQL/schema level data
types
3. call PL/SQL ERP proc passing populated pl/sql table type as IN OUT
parameter
4. take PL/SQL table type of Records and iterate to either pipe out through
pipeline function, OR expose as ref cursor, or as a nested table output
parameter (all of which are consumable by the Java Client) as the result.
easier said then done.. but I believe this to be the approach based on the
definition of the ERP proc you are dealing with.
corrections welcome.
Job
Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
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, 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@xxxxxxxxx> 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
---------------------------------
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
Other related posts:
