No problem... here's the situation:
We have a query tool which allows our users to select a collection of objects from our database based on a simple criteria they provide through our application's user interface. I work at an animation studio, so the user might ask for a list of all shots "done in animation". Shots trickle through the Animation department in an arbitrary order based on complexity, staffing, and numerous other variables.
Our query tool application code is built in such a way that the query it constructs "dumbs down" the request to look like this:
-- Query 1
select shot_id from shots where <programmatically constructed criteria from the query tool UI>;
Once I have this arbitrary list of shot_ids, I need to retrieve a ResultSet from which I can build all of the Shot objects:
-- Query 2
select C1, C2, C3 from shots
where shot_id in ( <the ResultSet from Query 1> );
This is a simplified example, but you get the point. Query 1 and Query 2 are loosely coupled and I can't combine them into one query without having a huge effect on the current codebase. I realize that Query 2 would work as a subquery of Query 1, but I don't have that luxury.
That being said, I am stuck having to run Query 2 once for each row in Query 1's ResultSet, or build a compond string representation of Query 1's ResultSet and construct Query 2 in an ad hoc fashion risking a SQL String that's too big for Oracle.
I'd love to be able to do something like this using JDBC:
PreparedStatement theStmt =
theCon.prepareStatement( "select <cols> from shots where shot_id in ?" );
theStmt.execute("CREATE TYPE shotidlist_type AS table OF number(8)");
oracle.sql.ArrayDescriptor theArrayDesc =
oracle.sql.ArrayDescriptor.createDescriptor( "shot_idlist_type", theConnection );
java.sql.Array theArray =
new oracle.sql.ARRAY( theArrayDesc, theConnection, theShotIds /*int []*/ );
theStmt.setArray( 1, theArray );
ResultSet theResult = theStmt.executeQuery();
... but I've had no luck with this and the more I think about it, I realize that what I'm trying to do is a fairly special case. I'm starting to wonder if it's possible at all?
Any help is greatly appreciated, thanks for reading this far ;-)
hernan
