Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.1.3

I need to pass an array of string to a stored procedure. The stored procedure is defined as:

FUNCTION findByIds(idArray ARRAY_TYPE)
RETURN SYS_REFCURSOR IS

rtd_cv SYS_REFCURSOR;
sql_stmt VARCHAR2(1000);
sql_where VARCHAR2(1000);

BEGIN

FOR i IN 1 .. idArray.COUNT LOOP
dbms_output.put_line(idArray(i));
END LOOP;

-- More processing code
RETURN rtd_cv;

END;

The named query is:

<sql-query name="SEARCH_BY_IDS_SP" callable="true">
<return class="com.mycompany.PublicTO"/>
{ ? = call findByIds(:Ids) }
</sql-query>


The test code is:

session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
Query query = session.getNamedQuery("SEARCH_BY_IDS_SP");
String[] ids = {"123","232","123"};

query.setParameter("Ids", ids);
List list = query.list();

session.clear();
session = null;
tx.commit();

But the above test code throws the following exception:

PLS-00306: wrong number or types of arguments in call to 'FINDBYIDS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



My question is how to bind a array of string to a parameter when calling stored procedure?

Thanks

Sean

There doesn't seem to be a SetParameter style function in IQuery that handles arrays.

There's SetParameterList that can take a ICollection but I'm not sure that would do what you want.

An alternative could be to modify your stored procedure to take in a single string and then pass it a comma-separated list of id values.
The stored procedure could then split this string into an array.

Update - looks like Oracle doesn't have a split function of its own. Fortunately, quite a few people have created their own:

http://www.google.com.au/search?q=Oracle+split+function