Sending an Array into a PL/SQL Procedure

Question

I have created a Web Service to send in a bunch of information to a PL/SQL procedure, however one of them is a array. What type do I use for this? I also want to put that array into a cursor after it comes in.

Answer 1

I don't have any experience with PL/SQL, so sorry if this doesn't apply. In MS SQL 2000/2005, there isn't a way to pass arrays into a procedure; I'm assuming PL/SQL has a similar limitation. The workaround I've used in the past is to pass in a delimited string (usually pipe delimited because commas were present in the data), and then have a function that can take a delimited string and break it up into a table result with one row per value. Then inside your procedure, you just call your split function passing it the delimited string and you have a table result that you can do whatever you want with (cursor over, join to other tables, etc).

EDIT: Just did a google for "PL/SQL table parameter" and had a few hits; Might be worth investigating to see if any of those results can help you.

Answer 2

This link might be of use. Or you can do what the other poster said and basically serialize your data into a string, pass it, and then unserialize it on the other end.