Richard, Yes, but the tricky part is doing something useful with a NUM_VARRAY (defined to be SQL type VARRAY elsewhere in the sample) inside the stored procedure itself. For example, I would like to take a VARRAY that I pass to an Oracle stored procedure and use it as a list of departmentIDs in a WHERE clause.
The original SQL looked something like this: SELECT * FROM INVOICE WHERE (DEPARTMENTID = 1 OR DEPARTMENTID = 2 OR DEPARTMENTID = 3) I need to achieve the same effect via the VARRAY, but don't know enough of Oracle's SQL to pull it off. I almost got it going using WHERE IN (subquery of VARRAY here), but we have clients with > 1000 departments. Our version of Oracle has a limit of 1000 items in a WHERE IN clause. I'd love to see something that worked. :-) == Ross == -----Original Message----- From: A mailing list for discussion about Sun Microsystem's Java Servlet API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of Richard Yee Sent: Thursday, January 24, 2002 1:13 PM To: [EMAIL PROTECTED] Subject: Re: Stored Procedures Marcos, Yes, you can use the CallableStatement.setArray method to set your in parameter. The Array parameter needs to be created using code like the following: // create a new ARRAY object int elements[] = { 300, 400, 500, 600 }; ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn); ARRAY newArray = new ARRAY(desc, conn, elements); This info was taken from the Oracle documentation. -Richard At 05:58 PM 1/24/2002 -0200, you wrote: >Question: > > Is it possible to use a store procedure (prepareCall interface) with >an Array as "IN" parameter? > > > Marcos > >___________________________________________________________________________ >To unsubscribe, send email to [EMAIL PROTECTED] and include in the body >of the message "signoff SERVLET-INTEREST". > >Archives: http://archives.java.sun.com/archives/servlet-interest.html >Resources: http://java.sun.com/products/servlet/external-resources.html >LISTSERV Help: http://www.lsoft.com/manuals/user/user.html ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html ___________________________________________________________________________ To unsubscribe, send email to [EMAIL PROTECTED] and include in the body of the message "signoff SERVLET-INTEREST". Archives: http://archives.java.sun.com/archives/servlet-interest.html Resources: http://java.sun.com/products/servlet/external-resources.html LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
