We are facing an issue while executing a stored procedure in hibernate.

Issue:
We have a stored procedure named “GET_OFFER_CATEGORIES_PRO” which takes “country” as an input argument and returns a ref cursor which contains categories and importers.
The problem is that for different countries passed, the number of columns which the SP returns will vary (because no of importers for each country varies).
So I am not able to map exact number of column names in mapping xml file.
For example - for certain country if it returns values for two importers, for certain other country it will return values for a three importers.
So we are confused how to write it in mapping xml file.

<sql-query name="getMoreMktInfo" callable="true">
<return-scalar column="catagory" type="java.lang.String"/>
<return-scalar column="imp1" type="java.lang.String"/>
<return-scalar column="imp2" type="java.lang.String"/>
<return-scalar column="imp3" type="java.lang.String"/>
{call GET_OFFER_CATEGORIES_PRO(?,:country)}
</sql-query>

If SP returns values for 3 importers the above snippet will work fine, but if only two importers are there then a SQL exception is thrown (invalid column name).

The stored procedure specification is:
create or replace PROCEDURE Get_offer_categories_pro (
REF_CURSOR OUT SYS_REFCURSOR,
I_COUNTRY_ID IN OFFER_OBJECT.COUNTRY_ID%TYPE)

Please help.

Can you modify the stored procedure, so that it always returns three columns, where the first column holds the category name, the second one holds the name of the importer and the third column holds the value for that category/importer combination?

If for example your current version of the stored procedure returns four columns and 2 lines like this:

The new version would now return three columns and six lines:That way, your stored procedure no longer returns a variable number of columns.

Thanks etwcn for the quick help.

Provided suggestion will make the column numbers fixed.
But business department needs that functionality only :(

If any one faced this issue earlier, Please guide.