Passing an Array in a PL/SQL Function/Procedure
Note: The code provided will not compile. It should only serve as a guide!
We had a performance issue and one of the solutions I came to to prevent excessive rountrip from my Java application to the database is to pass the things I need as an array to a store procedure. I won't cover the Java part here as I won't be pushing with it anyway, I'm just posting here for other people's reference.
To warn you though, passing an Array from my App to a PL/SQL stored procedure made my code vendor-dependent(i.e. Websphere dependent).
Inside a PL/SQL Package, you have to declare a TYPE as a TABLE of your TYPE:
TYPE VARCHAR2_ARR IS TABLE OF VARCHAR2(50);
Then, you can now use it as a parameter in your function/stored proc
PROCEDURE MY_PROC(MY_PARAM_ARR IN VARCHAR2_ARR)
IS
--LOOP LIKE YOU WOULD LOOP INSIDE A CURSOR
FOR TEMP_VAR IN MY_PARAM_ARR.FIRST .. MY_PARAM_ARR.LAST LOOP
--USE TEMP_VAR HERE
END LOOP;
END MY_PROC;
Now I'm gonna go for a comm-delimited approach for my parameter, parse that and see how it would benefit me.