Using stored procedures in a CORBA like way? (Oracle questions?)

The site that I currently work for has made some decisions recently that have had a large impact on client side development. Namely a lot of data sources are vending their data only through stored procedures. Their stored procedures expect output variables to be passed and to fill them in the stored proc.

This isn't necessarily a large problem but I would like to know how common this practice is together with the way they intend to use the stored procedures.

Specifically they return multiple data sets. I'll define a data set as a group of fields that typically belong to a single table. (Putting joins aside for the moment)

So in order to return data sets that don't have the same fields and same number of columns they return the data in this format.

Suppose we have two tables. Employees and Departments

EMP_ID, EMP_NAME DEP_ID, DEP_NAME, MANAGER_ID So in order to return this data from a _single_ stored procedure you see something like this as the output parameters.

number of employees
number of departments
empids
empnames
depids
depnames
managerids

Where each field is a comma separated list of the values for that "tables" field.

Firstly, my feeling about this is that it's a kludge because the client is forced to parse a linear string rather than binding the columns directly into values for each field and also you are limited to static allocation of the size of these lists. ie. these lists are just a varchar2 style array that's statically sized in the proc.

How common is this? It means _for us_ that when you cannot support out parameters at the client level you have to define a wrapper function that basically defines the output variables to be filled in, calls the stored proc and contatenates each output variable into a long string and return that string.

NOTE: we cannot walk a cursor on the client because we aren't using OCI directly. the only way we can see data returned from a function is from within the result set itself. Out parameters are not supported in the access layer we are using.  

Q. What is the maximum size of a string that you can return from an Oracle function?

-- 
It's Saturday not Sadurday ok!