I'm afraid I have no idea how to call PL/SQL from VB6 . . . for example how many rows of your big UNION query can you return at a time?

Multi row queries can use a simple cursor to fill the output array - http://www.csee.umbc.edu/help/oracle..._oview.htm#767
Single row queries use an INTO clause: SELECT xyz INTO my_var FROM dual;

Thanks for simplifying your code before posting it