Hello folks. I'm having an issue with getting parameters to work in an
Oracle CONNECT BY call, and I was wondering if this is a bug (and if
so, if anyone has come across simple work-arounds) or if my code is
just incorrect.
My Java data access object uses this class/function (I'm using the
Spring Framework):
public class SqlMapOrgDAO extends SqlMapClientDaoSupport implements OrgDAO {
public List getSiteLevelsByGroupId(int ceId, int groupId) throws
DataAccessException {
Map params = new HashMap();
params.put("ceId", ceId);
params.put("groupId", groupId);
List siteLevelList =
getSqlMapClientTemplate().queryForList("getSiteLevelsByGroupId",
params);
}
.....
My iBATIS map:
<select id="getSiteLevelsByGroupId" parameterClass="map" resultMap="siteLevel">
SELECT DISTINCT c.ce_depth_id, c.ce_depth_desc
FROM table_1 a,
table_2 c
WHERE a.absolute_depth = c.absolute_depth
AND a.group_id = c.group_id
AND a.absolute_depth >= 1
AND a.group_id = #groupId#
START WITH a.ce_id = #ceId#
AND a.group_id = #groupId#
CONNECT BY a.parent_ce_id = PRIOR a.ce_id
AND a.group_id = #groupId#
</select>
Only the last #groupId# causes the problem - it returns an empty list.
If I replace the last #groupId# with a hard-coded value that matches
the other #groupId# values, the query works and I get a list with the
correct rows. So basically, the value of groupId is not being passed
correctly to the last #groupId# reference, and it is suspect because
it is in a CONNECT BY statement, a situation that has resulted in the
same problem for my development team before.
I would greatly appreciate your assistance if you know anything about
this issue!
Thanks,
Mike
