Solved: Problem with Cursor in Weblogic

HI We are using weblogic 7 with oracle 9i with JDK 1.4.1

The problem is that when I use cursor in my query and execute it through JDBC (Java) I get following error

at weblogic.db.oci.OciCursor.getCDAException(OciCursor.java:259)
at weblogic.jdbc.oci.Statement.executeQuery(Statement.java:907)
at weblogic.jdbc.pool.PreparedStatement.executeQuery(PreparedStatement.java:54)
at com.atroad.clientadmin.dao.UserListPageDAO.getAllUsers(UserListPageDAO.java:441)
at com.atroad.clientadmin.bo.UserListPageBO.getAllUsers(UserListPageBO.java:80)
at com.atroad.clientadmin.action.UserListAction.loadData(UserListAction.java:74)
at com.atroad.clientadmin.action.BasePageAction.load(BasePageAction.java:192)
at com.atroad.clientadmin.action.BasePageAction.doSearch(BasePageAction.java:130)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)

We are using prepared statements and simply setting some Integers and Strings only. The query is attached as a code snippet. Please let us know why this happens and whats the solution.

private static final String searchOrgUsersByCapabilitiesQry = "select U.ID AS ID,  U.USERNAME AS USERNAME, U.FIRST_NAME AS FIRST_NAME, U.LAST_NAME AS LAST_NAME,"
 
            + " U.LAST_NAME ||','|| U.FIRST_NAME AS USERS,  U.TITLE AS TITLE,  U.DEPARTMENT AS DEPARTMENT,"
 
            + " U.EMAIL_ADDRESS AS EMAIL_ADDRESS,  U.CLIENT_ID, "
 
            + " Cursor(select ounit.ID AS ORG_ID,  ounit.NAME AS ORG_NAME, ounit.ID ||'|'|| ounit.NAME AS FMT_ORG"
 
            + " from Org_user ou,org_unit ounit"
 
            + " Where U.ID = ou.USER_ID(+)"
 
            + " AND   ounit.ID(+) = ou.ORG_UNIT_ID ) ORG_RS"
 
            + " from users U "
 
            + " where U.id in ("
 
            + " select User_id  from RBAC.RBAC_USER_ROLES"
 
            + " where role_id in (select Role_id from RBAC.RBAC_ROLE_CAPABILITIES"
 
            + " where capability_id in (select ID from RBAC.RBAC_CAPABILITY RC "
 
            + " Where NLS_UPPER(Capability_name) LIKEC :searchText)))"
 
            + " And client_id = :client_id And username not LIKEC :apiuser";

Open in new window