asked on

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

MikeOM_DBA

What is this "Cursor(..." thing ??????:

-- etc --
           + " 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(+)"
-- etc --???

Open in new window

MikeOM_DBA

It appears you have a mal-formed query.

Amit_Mudgal

ASKER

Hi Mike
Can you please correct me that where I might be wrong? Any help on this will be really great!!

Thanks in Advance!

ASKER CERTIFIED SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amit_Mudgal

ASKER

Hi Mike,
Thanks for your comment... I will investigate on this further and will update the thread if the problem is solved or I find something interesting.

Thanks for your help once again!!

Amit